Trouble with times.

[Jables]

I have a spreadsheet which contains transit times of a vehicles. These have a conditional format on the last cell to determine whether they're going to reach their destination early or late. The problem is.. Some of the times run through two days, i.e. leaves at 21:00 and arrives at 02:03. When this happens the conditional format reads this wrong as shows it as appearing late when its not.

Can you help? I have enclosed a screenshot to illustrate my problem.

 

[JeffreyWeir]

Can you attach a sample workbook? This will make it easier for someone to answer your question.

 

[Simi]

Yes a sample would help.
In the meantime you can try comparing both date and time, instead of just time.
use the same format that now() gives you. i.e. 4/30/2013 8:00
you can then add the transit time in hours:min

 

[Jables]

Hi, Thanks all for the responses, I have attached the workbook for you all to have a try.
The problem with the format option is that i would be to edit every date when i enter the date for every time enter, and having to use this document daily, this can be a tad tedious. So i was trying to work out a way in which this can be avoided. However this thats the only viable option, then this will have to do .View attachment John Burt's Departure Sheet for Internet.zip

 

[Simi]

hmm, I took to long writing my last reply and it was lost.
It seemed you had an error on calculating the latest departure time. So lets use a formula to figure that out.
Example replace the cell L3, with =IF(N3-M3<0,N3-M3+1,N3-M3)
and replace P3 with =IF(ISBLANK(O3),IF(L3+M3>1,L3+M3-1,L3+M3),IF(O3+M3>1,O3+M3-1,O3+M3))
also, you have a lot of conditional formatting.
you can format an entire range at once,
use a formula to determine which cells to format.
=(G3>E3)
then change the range to =$G$3:$G$18

Hope this gets you what you needed.