# advice on rounding or ceilng a 24 hour time value

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.

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

S

#### sammy

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

Z

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

S

#### sammy

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.