advice on rounding or ceilng a 24 hour time value

  • Thread starter Thread starter sammy
  • Start date Start date
S

sammy

Guys,

Appreciate advice on rounding up a 24 hour time values

I'm hoping for some advice on 2 slightly different roundup formulas one for
rounding to the hour and one to nearest 15 mins.

Thanks in advance

Sammy


current preferred
data rounding to nearest hour

21:51:13 22
22:56:23 23
23:56:02 00
00:58:00 01
01:54:59 02


current preferred
data rounding to nearest 15 mins

00:12:00 01:15
01:47:59 02:45
 
The following works for me, though the sample answers in the OP don't
look quite right for the second part (I'd think rounding 12 minutes
would be 15 minutes, not an 1 hr 15 min).
rounding to nearest hour =ROUND(24*A1,0)/24

rounding to nearest 15 mins
=ROUND(96*A1,0)/96

In my example, all the cells are "time" values.

The above assumes the requirement is for "nearest" not "roundup." If
that's wrong, check out the ROUNDUP() function.
 
thanks for your help done the trick :)


zvkmpw said:
The following works for me, though the sample answers in the OP don't
look quite right for the second part (I'd think rounding 12 minutes
would be 15 minutes, not an 1 hr 15 min).

=ROUND(96*A1,0)/96

In my example, all the cells are "time" values.

The above assumes the requirement is for "nearest" not "roundup." If
that's wrong, check out the ROUNDUP() function.
 
Back
Top