Rounding to 6 Minute Increments for Timesheet

[JRose0303]

Our time keeping systems utilizes a 6 minute rule. If your time-in is 8:25, I want it to automatically round the total to the nearest 6 minute increment.

TIME IN: 8:25
TIME OUT: 12:00
TOTAL: 4.50

This is the current formula in the total cell:
=IF((OR(C8="",C7="")),0,IF((C8<C7),((C8-C7)*24)+24,(C8-C7)*24))

How do I modify this so it rounds to the nearest 6 minute increment?

[dilipandey]

Hi JRose0303,

For 6 minutes rounding, use the below formula:-

Please Login or Register  to view this content.

Where a1 is the cell which you want to round. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

---------- Post added at 08:29 PM ---------- Previous post was at 08:27 PM ----------

Missed the increment part..

If you want the time to increment (up) always, use below formula

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

[shg]

One way:

=IF(COUNT(C7:C8)<2, "", ROUND(24*MOD(C8-C7 + (C7<C8), 1), 1))

[Ace_XL]

Rounding up ..

=CEILING(IF((OR(C8="",C7="")),0,IF((C8<C7),((C8-C7)+12),(C8-C7))),6/1440)