Conditional formatting multiple rules

1. 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. 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%)

3. Re: Conditional formatting multiple rules

Originally Posted by NBVC
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. 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. 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. 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!

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

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