+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting multiple rules

  1. #1
    Registered User
    Join Date
    06-16-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Conditional formatting multiple rules

    I am trying to color code a worksheet for project status based on % complete and Due date.

    My logic
    If Due date is less than 7 days from today and % complete is less than 75 highlight red
    If Due Date is less than 14 days from today and % complete is less than 50 highlight yellow
    Else highlight green.

    Here are the rules I used in conditional formatting:


    The first rule works however it never executes the second rule (even if I switch them).
    =IF(and($F$9<Today()-7, %G%9<75%)
    =IF(and($F%9<Today()-14, %G%9<50%)

    I haven't gotten to Else highlight green.

    Your help & expertise is greatly appreciated.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting multiple rules

    Are you looking for days in the future or in the past...

    I think you want future days... so Try:

    =AND($F$9>=TODAY(),$F$9<TODAY()+7,$G$9<75%)

    and

    =AND($F$9>=TODAY(),$F$9<TODAY()+14,$G$9<50%)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-16-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Conditional formatting multiple rules

    Quote Originally Posted by NBVC View Post
    Are you looking for days in the future or in the past...

    I think you want future days... so Try:

    =AND($F$9>=TODAY(),$F$9<TODAY()+7,$G$9<75%)

    and

    =AND($F$9>=TODAY(),$F$9<TODAY()+14,$G$9<50%)

    I want to highlight the project Red if the due date is 7 days or less away and the % complete is less than 75.

    The formula works however it never seems to test for the second condition. It is either red or nothing.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting multiple rules

    To me $F$9<Today()-7 means the date in F9 is less than 7 days ago... is that what you want or do you want to check if F9 is less than 7 days from now (into the future)... ie. if F9 had date JUNE 17, 2011, then you would get TRUE result... but if F9 say JUNE 15 or anything ealier or if F9 has anything later than June 23rd you would get false.

  5. #5
    Registered User
    Join Date
    06-16-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Conditional formatting multiple rules

    Future
    If the due date is within 7 days or less and % complete is less than 75% - highlight red
    If the due date is within 14 days or less and % complete is less than 50% - highlight yellow

    The logic seems to work for the first condition. If listed in the order above it will only return red or no highlight.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting multiple rules

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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