rounding up time to quarter hour increments

[kdp145]

i have a column with date and time(column A) and i want to roundup the
time to every quarter and then separate the date(column B) and time
(column C)

A B C
3/14/2006 10:43PM 3/14/2006 10:45PM
3/14/2006 11:34PM 3/14/2006 11:45PM
3/14/2006 11:52PM 3/15/2006 12:00AM

note that since 11:52PM rounds up to 12:00, i want to change the date
to the next day.

 

[daddylonglegs]

There's more than one way to do this depending on whether you're just
concerned about what displays or if you want to use the times and dates
generated for calculations..

For the first in both B1 & C1

=CEILING(A1,"00:15")

format B1 as date, C1 as time

OR....

In B1

=INT(CEILING(A1,"00:15"))

format as date

in C1

=MOD(CEILING(A1,"00:15"),1)

format as time

edit: or even.....

to give text results

=TEXT(CEILING(B1,"00:15"),"m/d/yyy")

and

=TEXT(CEILING(A1,"00:15"),"hh:mm AM/PM")