Trouble with times.

Jables

New member
Joined
Apr 25, 2013
Messages
2
Reaction score
0
Points
0
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.

Worksheet Scan.jpg
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Can you attach a sample workbook? This will make it easier for someone to answer your question.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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

New member
Joined
Apr 25, 2013
Messages
2
Reaction score
0
Points
0
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 :D.View attachment John Burt's Departure Sheet for Internet.zip
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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.
 
Top