Rounding up time issues

[August]

In cells N70:N76 I have formulas that calculate hours (cells are formatted hh:mm)
In N77 I have the formula =CEILING(SUM(N70:N76),"00:15") to round up the total to the nearest 15mins
The issue I'm having is that if the total of the range N70:N76 is 00:30 the formula in N77 sometimes gives 00:30 and sometimes 00:45
I've checked for hidden characters and I've also tried the formulas below to no avail
=CEILING(SUMPRODUCT(N70:N76)*24*60,15)/24/60
=CEILING(SUM(N70:N76)*24*4, 1)/(24*4)
=CEILING(SUM(N70:N76)*24*60,15)/24/60

Any advice would be greatly appreciated

 

[6StringJazzer]

If a time is displayed as 00:30, the actual time could be 00:30 plus some number of seconds. That is greater than 00:30 so will result in a CEILING of 00:45.

There are also real-number issues that could be at play.

It would be helpful to have some samples of your data (a link to a file would be ideal, but listing the values would be good enough), and also the exact underlying values, not just the displayed hh:mm formats.

 

[August]

Thanks for reply
The times in N70:N76 are the result of numerous calculations that form part of a time sheet calculator.
The input times are all entered via Data Validation drop downs and when I cleared and re-entered the validation cell linked to N70> formulas the rounding error cleared.
Still doesn't explain the original hit and miss of the result, but it's working correctly now having tested extensively
Many thanks again for taking the time to reply

 

[6StringJazzer]

If you are doing calculations on time values it is very likely that you have values that do not fall on exact minute boundaries. For example, 00:30 is stored as 0.0208333333333333. If a calculation results in 0.0208333333333334 it will look like 00:30:00 but is actually 30 minutes plus 0.00000000001 seconds, so if you apply your CEILING formula the result will be 00:45.

This is just an inherent consequence of doing real-number arithmetic in base 10.

 

[August]

I thought so, when looking at formula evaluation your 2nd value for 00:30 appears in some instances.
My original suggestion to the team here was to only allow time input in 15mins intervals and then there would be no need to round up, that might be the only way to go

Thanks again for your time, much appreciated

 

[6StringJazzer]

One option would be to round to nearest minute before applying the CEILING function. I should have thought of that before.

=CEILING(MROUND(SUM(N70:N76),"00:01"),"00:15")

 

[August]

One option would be to round to nearest minute before applying the CEILING function. I should have thought of that before.

=CEILING(MROUND(SUM(N70:N76),"00:01"),"00:15")

Thanks, I'll try that and see what happens

 

[August]

Thanks, I'll try that and see what happens

Thank you so much, that works perfectly !!!

 

[6StringJazzer]

You must have done very extensive testing

 

[August]

V
The SAG strike took the pressure off a little
Now every one is hurry hurry hurry!!!
Your solution has cleared all errors, so once again many thanks