round up time unless already at the desired time value

I have searched and found close to but not the right answer for this.

I have a tv schedule with a start time, an end time, and a duration for each episode. (Column C) contains start time, (Column D) contains end time, and (Column F) contains duration. The only values I enter are the first start time (C2) and each duration(Column F). The end times (Column D) are calculated by adding the start time and the duration, and then all subsequent cells in (Column C) need to be rounded up to the next half hour, unless the previous episode lands on an hour or half hour. Basically, I can't start a new show at 2:29:30, I have to start it at 2:30.

I started with this formula:

=IF(F2="","",IF(MINUTE(C2)<>30,CEILING(C2,0.02083333),C2))

This works for some cases, but when a show ends on the hour, it makes the next start time happen at the next half hour. How can I round up to the nearest half hour (either 30 minutes or the hour)?

example:
(C2) 5:00 AM (D2) 6:00:00 AM (F2) 1:00:00
(C3) 6:30 AM



I want (C3) to be 6:00 AM

So then I tried this:

=IF(F2="","",IF(MINUTE(C2)=30,C2,IF(MINUTE(C2)=0,C2,CEILING(C2,0.02083333))))


And it seemed to work, until I entered a place holder with a duration of 1 second. Since the minute still equaled 30, the next start time didn't round up.

example:

(C2) 5:30 AM (D2) 5:30:01 AM (F2) 00:00:01
(C3) 5:30 AM

In this case I would need (C3) to equal 6:00 AM

I tried combining MINUTE and SECOND formula items, but I got an error every time, and couldn't figure out how to structure the argument.

Thanks in advance for any help.

You can use this in C3 and drag down.See the attached file.

Please Login or Register  to view this content.

Thanks for the reply. Unfortunately, that result added an hour to my next start time, and didn't round to any hour or half hour mark. It put a ceiling on the duration, and then added that to the end time. I kept searching a little this morning and found this, which so far is working. I have to throw some odd times at it, but so far so good.



=IF(F2="","",CEILING(C2,"0:30"))

I will close this thread and provide a link to the thread that helped solve it for me. Thanks again for your response. I appreciate the effort.

http://www.excelforum.com/excel-gene...half-hour.html