Results 1 to 5 of 5

Thread: Trouble with times.

  1. #1

    Trouble with times.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Click image for larger version. 

Name:	Worksheet Scan.jpg 
Views:	118 
Size:	105.1 KB 
ID:	1300

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Can you attach a sample workbook? This will make it easier for someone to answer your question.

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  4. #4
    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 .John Burt's Departure Sheet for Internet.zip

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •