+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting of two cells containing times based on difference between them.

  1. #1
    Registered User
    Join Date
    01-21-2018
    Location
    Northampton, England
    MS-Off Ver
    2010
    Posts
    61

    Conditional Formatting of two cells containing times based on difference between them.

    Evening all,

    I'm looking for some help regarding conditional formatting.

    As per the below image the rules should be as follows

    1- Arrival time - (Green) = earlier or at the same time as the planned arrival time / (Red) = Any Time past Planned Arrival Time.
    2- Finish Load Time - (Green) = earlier or up to 3 minutes after CPT / (Red) 4 minutes after CPT.
    3- Actual Departure Time - (Green) = Earlier or equal to Allowed Departure time / (Red) = After Allowed Departure Time

    Capture.PNG

    Any Help would be greatly appreciated using conditional formatting.

    Thanks all.

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Conditional Formatting of two cells containing times based on difference between them.

    Format all cells as green and create CF only for RED . it could be even one formula but because of mix between column it will a little bit difficult then 3 separated CF that simple.
    Small example from your side help to show.

  3. #3
    Registered User
    Join Date
    01-21-2018
    Location
    Northampton, England
    MS-Off Ver
    2010
    Posts
    61

    Re: Conditional Formatting of two cells containing times based on difference between them.

    Would be helpful if would be able to supply a formula as this is where I'm having the difficulty. I know it should be fairly simple but been at it an hour now trying to figure it out.

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Conditional Formatting of two cells containing times based on difference between them.

    sorry he condition is not so simple because next day time. Is there max delta between fact and plan if fact before.
    23:29 - early 00:01 but value is less.

  5. #5
    Registered User
    Join Date
    01-21-2018
    Location
    Northampton, England
    MS-Off Ver
    2010
    Posts
    61

    Re: Conditional Formatting of two cells containing times based on difference between them.

    Unfortunate as its based around transports there is no maximum or minimum delta

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Conditional Formatting of two cells containing times based on difference between them.

    How the next day can be determined? If you use date time format it is no problem to see that 01/13/2020 00:01 after 01/12/2020 23:29 ? but without date it is impossible, sorry. In the case we say that fact time never could be less then plan time more then 1h or … then I can help.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by BMV; 01-13-2020 at 03:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formatting based on % difference
    By Raywoot in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2015, 06:07 AM
  2. conditional formatting based on time difference
    By arindamsenaxa in forum Excel General
    Replies: 4
    Last Post: 11-28-2014, 10:58 AM
  3. [SOLVED] Conditional formatting based on the difference value between figures in two cells
    By Karczoch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2014, 07:50 AM
  4. Conditional Formatting based on difference in Cells
    By QuietLife in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-26-2013, 06:25 AM
  5. Conditional Formatting help - based on difference between two cells
    By oscarbunny in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2012, 10:00 AM
  6. Conditional formatting based on a percentage difference
    By Dan27 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-13-2010, 04:53 PM
  7. Conditional Formatting Based on Percent Difference
    By dontcare in forum Excel General
    Replies: 1
    Last Post: 06-18-2010, 05:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1