Timeclock: rounding hours up and down

[Vinay Patel]

I'm trying to create a spreadsheet where an employee's
time on the job is calculated. I have created cells with
clock in, clock out, and total time, but what i need now
is for Excel to round the clock in time down to the hour
if it is 6 minutes past the hour, but if it 7 minutes
have passed then I want the clock in time to round up to
15 minutes past the hour.

Example:
If Jan comes in at 8:06 and this figure is entered into
Excel, it should be read as a clock in time of 8:00.
However, if jan comes in at 8:07 and this is the time
entered into excel it should be rounded up to 8:15.

Please Help

Thanks

 

[Harald Staff]

Hi

Formula solution:
=ROUND((A1*96+1/15),0)/96

There are 96 15-minutes in a day, so that's what the number does. Change to 48 for half
hours. The 1/15 adds a minute -normally you'd round down at 07 and up at 08. Remove that
part if you redecide.