Excel 2007 : Rounding hours up and down for 15minute clock in/out intervals?

Anyone able the two required formulas in row 5 & 7 in the attached?

Hours Totals & Rounding.xlsx

Clock IN's, need to be rounded UP to the nearest 15 minute interval
Clock OUT's, need to be rounded DOWN to the nearest 15 minute interval

Thanks

I haven't opened your file, but:

Formula:

Please Login or Register  to view this content.

will always round down to the nearest 15 mins, and

Formula:

Please Login or Register  to view this content.

will always round up.

Originally Posted by stunn

Slight amendment:

Formula:

Please Login or Register  to view this content.

is required if the time to be rounded down is only a few minutes after midnight.

That causes the total for the day to change to #####

see here Hours Totals & Rounding.xlsx

Originally Posted by dilipandey

Hi benoj2005,

Try using below formula:-

=MROUND(I16,0.0104166666666667)

where I16 cell has the time

See attached:- Attachment 168575


Regards,
DILIPandey

<click on below 'star' if this helps>

That works for clock IN's but not for Clock OUT's, which need to round DOWN, not UP

Originally Posted by stunn

You have 00:00-15:15, which may look fine to us humans, but for Excel it results in a negative time, which it cannot handle. To get around this, you would have to add 1 day when "Interval Out" is greater than "Interval In". i.e. when the shift goes over into the next day. Try this in C9:

Formula:

Please Login or Register  to view this content.

and, of course, this in C10:

Formula:

Please Login or Register  to view this content.

Thanks, that worked.

You'd think they'd have some functions for dealing with time like this.

All sorted, solution here if anyone wants for future Hours Totals & Rounding.xlsx