Clock-In Time Rounding

[nharvey]

I have found other threads similar to this but not exactly what i need.
So the goal is to set up a rounding rule so that when a time is entered it is rounded up the the 00:15 interval at 00:05, or if they clock in @ 00:35 it will round up to 00:45. and inversely if they clock in @ 00:04 it will round down to 00:00 or clock in @ 00:34 it will round down to 00:30.

help would be much appreciated

[shg]

Welcome to the forum.

=CEILING(A1 - "0:04:59", "0:15")

[nharvey]

thanks works amazing

[nharvey]

if i have to do the same thing but round down so if clock out at 00:56 rounds up to 01:00 or if clock out at 00:53 rounds down to 00:45

[shg]

If people clock in in the vicinity of midnight, use

=MOD(CEILING(A11 + "23:55:01", "0:15"), 1)

[nharvey]

than might of not made sense

clock out: Rounded to:
3:12 3:15
3:10 3:00
3:27 3:30
3:26 3:15

[shg]

Not on my computer:

Please Login or Register  to view this content.

I changed the cell reference to A11, that may have confused you.

[nharvey]

the formula you gave me to start worked perfect for up to the next 15 min increment after 4 minutes. now i need to do the opposite for clock out if they clock out at 02:11:00 it need to round down to 2:00 and same for every 15 min increment

[nharvey]

I got it thanks for your help