3

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?

brettdj
  • 54,857
  • 16
  • 114
  • 177
Ryan Ellis
  • 440
  • 2
  • 5
  • 18

2 Answers2

3

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......

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Barry, spot on!! That's exactly what was happening. I was doing a subtraction, END TIME - START TIME and this added the rounding errors you described. Now I know why this was all happening in the first place thanks for the clarification!! – Ryan Ellis Oct 12 '12 at 10:29
2

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

brettdj
  • 54,857
  • 16
  • 114
  • 177
  • I love Stack Exchange, but I love you more <3 Worked Perfectly and also accounts for hours as well! – Ryan Ellis Oct 12 '12 at 05:30
  • @RyanEllis thx :) This link has a bit more on ROUND and CEILING that you may find interesting. http://stackoverflow.com/q/8587474/641067 – brettdj Oct 12 '12 at 05:52