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

Hope what I say makes sense I think its a rounding function that I need to
use.
Sammy
"sammy" <(E-Mail Removed)> wrote in message
news:snCco.4391$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:9b6aa66e-3fef-4bb9-8846-(E-Mail Removed)...
> 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.