Round up a time to the next 15 minutes

Hi all,

I have a column of numbers in [hh]:mm format. Now I want to round up each
time to the next 15 minutes, e.g.

15:43 -> 15:45
15:46 -> 16:00

The ROUNDUP function does not provide a direct solution for me. Any
suggestion? Thanks a lot.

Frederick Chow
Hong Kong.

One way:

=CEILING(A1,1/96)



XL stores times as fractional days, so 15 minutes = 1/96 day.

In article <eWTewVCQGHA.1088@tk2msftngp13.phx.gbl>,
"Frederick Chow" <PleaseRemoveThishkmusc@yahoo.com> wrote:

> Hi all,
>
> I have a column of numbers in [hh]:mm format. Now I want to round up each
> time to the next 15 minutes, e.g.
>
> 15:43 -> 15:45
> 15:46 -> 16:00
>
> The ROUNDUP function does not provide a direct solution for me. Any
> suggestion? Thanks a lot.
>
> Frederick Chow
> Hong Kong.

=ROUNDUP(A2*96;0)/96

--
HTH
--
AP

"Frederick Chow" <PleaseRemoveThishkmusc@yahoo.com> a écrit dans le message
de news:eWTewVCQGHA.1088@tk2msftngp13.phx.gbl...
> Hi all,
>
> I have a column of numbers in [hh]:mm format. Now I want to round up each
> time to the next 15 minutes, e.g.
>
> 15:43 -> 15:45
> 15:46 -> 16:00
>
> The ROUNDUP function does not provide a direct solution for me. Any
> suggestion? Thanks a lot.
>
> Frederick Chow
> Hong Kong.
>
>

Oh! Thanks for reminding me the CEILING and FLOOR function can do the
tricks. Thanks for your reminder.

Fredeick Chow
Hong Kong
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-9706CC.00463705032006@msnews.microsoft.com...
> One way:
>
> =CEILING(A1,1/96)
>
>
>
> XL stores times as fractional days, so 15 minutes = 1/96 day.
>
> In article <eWTewVCQGHA.1088@tk2msftngp13.phx.gbl>,
> "Frederick Chow" <PleaseRemoveThishkmusc@yahoo.com> wrote:
>
>> Hi all,
>>
>> I have a column of numbers in [hh]:mm format. Now I want to round up each
>> time to the next 15 minutes, e.g.
>>
>> 15:43 -> 15:45
>> 15:46 -> 16:00
>>
>> The ROUNDUP function does not provide a direct solution for me. Any
>> suggestion? Thanks a lot.
>>
>> Frederick Chow
>> Hong Kong.

Thanks very much for your alternative version! Though more obscure, your
version does make sense to me.

Frederick Chow
Hong Kong.

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:ekSYnmCQGHA.5924@TK2MSFTNGP09.phx.gbl...
> =ROUNDUP(A2*96;0)/96
>
> --
> HTH
> --
> AP
>
> "Frederick Chow" <PleaseRemoveThishkmusc@yahoo.com> a écrit dans le
> message
> de news:eWTewVCQGHA.1088@tk2msftngp13.phx.gbl...
>> Hi all,
>>
>> I have a column of numbers in [hh]:mm format. Now I want to round up each
>> time to the next 15 minutes, e.g.
>>
>> 15:43 -> 15:45
>> 15:46 -> 16:00
>>
>> The ROUNDUP function does not provide a direct solution for me. Any
>> suggestion? Thanks a lot.
>>
>> Frederick Chow
>> Hong Kong.
>>
>>
>
>

A slight variation to perhaps make the purpose a little more transparent

=CEILING(A1,"00:15")