Clock-In Time Rounding

**nharvey** · 03-10-2010, 03:43 PM

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** · 03-10-2010, 03:48 PM

Welcome to the forum.

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

**nharvey** · 03-10-2010, 03:54 PM

thanks works amazing

**nharvey** · 03-10-2010, 03:57 PM

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** · 03-10-2010, 04:02 PM

If people clock in in the vicinity of midnight, use

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

**nharvey** · 03-10-2010, 04:07 PM

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** · 03-10-2010, 04:10 PM

Not on my computer:

      -A-- -B--
  1   3:12 3:15
  2   3:10 3:15
  3   3:27 3:30
  4   3:26 3:30

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

**nharvey** · 03-10-2010, 04:22 PM

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