advice on rounding or ceilng a 24 hour time value

[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

 

[sammy]

Hope what I say makes sense I think its a rounding function that I need to
use.
Sammy

 

[zvkmpw]

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.

 

[sammy]

thanks for your help done the trick

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.