Excel 2007 : Conditional Formatting_change cell color for dates

**TinaS** · 11-22-2010, 06:46 PM

I need help with conditional formatting in Excell 2007. I have the Expected Date in column B and the Actual Date in Column C. I need to create the below functions:

1. If Actual Date is blank and Expected Date is today or later: Actual Date cell is RED.
2. If Actual Date is blank and Expected Date is 1-14 days before today: Actual Date cell is YELLOW.
3. If Actual Date cell has a date, but it is later than Expected Date: Actual Date cell is ORANGE
4. If Actual Date cell has a date and it is on or before Expected Date: Actual Date cell is GREEN.

Any assistance would be greatly appreciated.

TinaS

**DonkeyOte** · 11-23-2010, 03:44 AM

Originally Posted by TinaS

I need help with conditional formatting in Excell 2007

Please modify your profile in line with the above (presently states 2003) - you would not be able to do the above prior to 2007 given there are only 3 conditional formats plus standard available.

With C1:Cn selected try applying Formula based rules of:

Rule 1: Red
=AND($C1="",$B1>=TODAY())

Rule 2: Yellow
=AND($C1="",(TODAY()-$B1)<=14)

Rule 3: Orange
=AND(ISNUMBER($C1),$C1>$B1)

Rule 4: Green
=AND(ISNUMBER($C1),$C1<=$B1)

Modify references to row 1 as necessary of course.

If in doubt - post a sample file.