CODEWITHSUNDEEP
excelexcel-formularounding
Ryan Ellis
Ryan Ellis

Reputation: 440

Round Excel Time Difference to Next 15 Minute Interval

I have a start time and an End Time in Excel:

Start Time 9:15 PM

End Time 9:30 PM Time Spent 0:15

I'm looking to round up to the next 15 minute increment, and are using the formula:

=ROUNDUP(A1*96,0)/96

However, this rounds up the example data above from 0:15 to 0:30 (It should stay at 0:15 because it's a quarter hour already)

How to I avoid the rounding if the time difference is ALREADY on a quarter hour?

Upvotes: 3

Views: 11434

Answers (2)

barry houdini
barry houdini

Reputation: 46331

I would normally expect the original formula to work. When I test in Excel 2007 with manually input time values that are always whole minutes the original formula with ROUNDUP, brettdj's formula and the following formula:

=CEILING(A1,"0:15")

.....all give me the same results

....but if the times are derived from some sort of formula calculation then excel floating point arithmetic can cause very small rounding errors so that 0:15 is deemed to be very marginally above that value (although it would still display as 0:15:00.000) and is therefore rounded up to 0:30.

Because brettdj's suggestion only looks at hours and minutes that problem is avoided but it will round 0:15:59 to 0:15 too, so this approach might be preferable

=CEILING(MROUND(A1,"0:0:01"),"0:15")

That rounds the original time to the nearest second with MROUND (thus dealing with any floating point errors) and then rounds up to the next quarter hour

Edit: if you are using a formula to get the A1 time value it might be worth incorporating some rounding within that formula, e.g. wrap formula in MROUND like this to round to the nearest second

=MROUND(formula,"0:0:01")

It may look like that does nothing in terms of changing the formula results but that will potentially avoid any further floating point issues with any other calculations you might need to do.....and then your original formula with ROUNDUP should work......

Upvotes: 3

brettdj
brettdj

Reputation: 55672

You could use
=HOUR(A1)/24+CEILING(MINUTE(A1),15)/(24*60)

which converts a time in A1 of - 9:15 PM to 9:15 PM - 9:16 PM to 9:30 PM

etc

Upvotes: 2

Related Questions