+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting Formula - Value plus difference between two dates

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Question Conditional Formatting Formula - Value plus difference between two dates

    Hello, I'm stuck with a formula I need for conditional formatting. I have several thousand records over several work sheets I need to check, and think conditional formatting is the way to go.

    So in column P I have the 'Outcome' field (which is typically Continuing, Achieved or Withdrawn). Q,R and S are date fields. Depending on the the outcome field I need to check the difference between Q-R or Q-S and highlight accordingly.

    I'm able to do one or the other but can't see how to check depending on the Outcome field. I appreciate I probably need to have multiple conditional formats rather than just one formula, which I am happy about amending.

    Any ideas would be gratefully received.

  2. #2
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Conditional Formatting Formula - Value plus difference between two dates

    I've drafted a small example, hope it helps.
    Attached Files Attached Files

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Conditional Formatting Formula - Value plus difference between two dates

    Hi,

    Formula for green might be
    =OR(AND($A2="Withdrawn",$D2-$B2<56),AND($A2="Achieved",$D2-$C2<90))
    For amber
    =OR(AND($A2="Withdrawn",$D2-$B2<64),AND($A2="Achieved",$D2-$C2<101))
    For red
    =OR(AND($A2="Withdrawn",$D2-$B2>63),AND($A2="Achieved",$D2-$C2>100))
    Make sure the rules appear in that order and that Stop if True is selected for each one.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Conditional Formatting with IF-THEN formula using DATES
    By GottaQ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2015, 01:52 PM
  2. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  3. [SOLVED] formula for conditional formatting (count of dates)
    By joeldlow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 05:04 AM
  4. default conditional formatting formula for dates
    By wormssss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 03:49 PM
  5. Formula or conditional formatting for dates
    By Phillipengela in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 02:11 PM
  6. [SOLVED] Conditional Formatting or formula for comparing dates
    By sweetds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 10:34 AM
  7. Conditional Formatting - Difference between dates
    By Gohan51D in forum Excel General
    Replies: 5
    Last Post: 03-14-2006, 06:10 AM

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