Hi Folks
Is there anyway of rounding up and rounding down time values please.
For example:
Start Time: 04:35 and I want it to be rounddown to 04:30
Finish Time: 17:50 and I want it to be roundup to 18:00
Regards
David
iMac Line (2012 and Later)
If you have durations in, say, A2 and C2, then you can do something like this:
In B2, with the original duration in A2:
=DURATION(,,,FLOOR(DUR2MINUTES(A2),15))
In D2, with the original duration in C2:
=DURATION(,,,CEILING(DUR2MINUTES(C2),15))
If this still isn't what you are looking for you could try posting a screenshot.
SG
If you have durations in, say, A2 and C2, then you can do something like this:
In B2, with the original duration in A2:
=DURATION(,,,FLOOR(DUR2MINUTES(A2),15))
In D2, with the original duration in C2:
=DURATION(,,,CEILING(DUR2MINUTES(C2),15))
If this still isn't what you are looking for you could try posting a screenshot.
SG
Assuming you want to round up/down to the quarter hour (15 minutes), then one way is to do something like this:
The formula to round down, in B2, with the original time in A2::
=TIME(HOUR(A2),FLOOR(MINUTE(A2),15),SECOND(A2))
The formula to round up, in D2, with the original time in C2:
=TIME(HOUR(C2),CEILING(MINUTE(C2),15),SECOND(C2))
The formulas are the same except that one uses FLOOR and one uses CEILING.
If your region uses , as a decimal separator replace the , in the formulas with ; .
SG
Thank you SG
I have this error: The function “HOUR” expects a date but found “6:50”.
My whole table is setup using DURATION data format, could this be the problem.
Confusing. In your post you state you want to round up or round down "time values". So you mean those are not date-time but are duration?
SG
Thank you SG. Yes, I should have stated Duration and not Time. Thanks for pointing that out.
The formula you provided has done the trick.
Thank you very much for your help.
Time ROUNDUP or ROUNDDOWN
