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.


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522319

For column B use "=INT(A1)",
For column C "=TIME(HOUR(A1),INT(MINUTE(A1)/15+1)*15,0)
Adjust the format of the cells to what you want to show.
---

"kdp145" wrote:


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.


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522319

i tried the formula you suggested, but they dont work....the date doesnt
change to the next day when anything greater than 11:46PM is incremented
to 12:00AM. the time doesnt work in some cases. for ex: if time is
11:23PM, it will increment it to 11:30PM, which is fine but if the time
is 11:30PM, it increments to 11:45PM instead of staying at 11:30PM.

anybody have any other suggestions?


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522319

I've tweaked them a little, you can check if they work now:
Column B: =IF(C10=0,INT(A10)+1,INT(A10))
Column C: =TIME(HOUR(A10),INT((MINUTE(A10)-1)/15+1)*15,0)
---

"kdp145" wrote:


i tried the formula you suggested, but they dont work....the date doesnt
change to the next day when anything greater than 11:46PM is incremented
to 12:00AM. the time doesnt work in some cases. for ex: if time is
11:23PM, it will increment it to 11:30PM, which is fine but if the time
is 11:30PM, it increments to 11:45PM instead of staying at 11:30PM.

anybody have any other suggestions?


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522319

thnx for your help...i was able to fix the problem using the following
formula:

B1 = A1+TIME(0,(CEILING(MINUTE(A1),15)) - MINUTE(A1),0))
C1 = equals the same thing as B1

then i formatted B1 to be date and C1 to be time


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522319

That's complicating things more than really necessary, this would
suffice

=CEILING(A1,"00:15")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522319