+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting formula problem

  1. #1
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    686

    Conditional Formatting formula problem

    IF A1 date less than today and B1 = Working
    Than A1 date colour RED


    IF A1 date from today to next 9 days and B1 = Working
    Than A1 date colour ORANGE

    I apply below formulas in conditional formula, but not working properly.

    =IF(AND($A1 < TODAY()),$D1="WORKING")
    RED


    =IF(AND(TODAY()+9 > =$A1),$B1="WORKING")
    ORANGE

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,040

    Re: Conditional Formatting formula problem

    In your first formula you have

    D1="WORKING"

    But your description says B1
    Your formula should also be this

    =(AND($A1 < TODAY(),$B1="WORKING"))

    Your second formula should be

    =(AND(TODAY()+9 > =$A1,$B1="WORKING"))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    686

    Re: Conditional Formatting formula problem

    Mistakenly but D1, but actually its B1,

    RED one working,

    but when I apply ORANGE one, then RED one not working
    Attached Files Attached Files
    Last edited by rajeshn_in; 09-22-2016 at 08:41 AM.

  4. #4
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    686

    Re: Conditional Formatting formula problem

    please check attachment

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,040

    Re: Conditional Formatting formula problem

    Change the order of the formulas, use the up arrow to make the red one the first formula, the orange one is taking precedent.

  6. #6
    Registered User
    Join Date
    09-21-2016
    Location
    Isle of man
    MS-Off Ver
    10
    Posts
    6

    Re: Conditional Formatting formula problem

    Hi

    I have used this to create a traffic light system on a spread sheet. I have cheated a little as I fill the cell with green and put two formulas in to change to amber and red

    =AND(D4>=TODAY()-89,D4<=TODAY()-75)

    =D4<=TODAY()-90

    the second one uses todays date as a reference and changes the cell red after 90 days

    the first one changes the colour of the cell amber if its between 75-89 days after the avent

  7. #7
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    686

    Re: Conditional Formatting formula problem

    Thank you very much

+ 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 problem- not all cells seem to use the formula
    By RattlerSkin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2016, 09:34 AM
  2. [SOLVED] Conditional Formatting formula for shading problem
    By johnsonbj22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-25-2015, 12:58 PM
  3. [SOLVED] Conditional Formatting - Formula Problem?
    By KenMcN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 03:00 PM
  4. [SOLVED] formula problem w/ conditional formatting
    By edwinculanding in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 05:20 AM
  5. [SOLVED] problem with logical formula for conditional formatting
    By nsterng1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-30-2012, 04:59 PM
  6. Conditional Formatting formula problem
    By ddub25 in forum Excel General
    Replies: 3
    Last Post: 01-21-2010, 08:24 AM
  7. Conditional formatting with dates formula problem.
    By russell.estes@gmail.com in forum Excel General
    Replies: 8
    Last Post: 03-05-2005, 08:06 PM

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