Excel – How to use Conditional Formatting based on due dates in Excel 2013

conditional formattingmicrosoft excelmicrosoft-excel-2013

I am trying to format cells in Excel 2013. I want the cells to get colored as green, yellow, and red based on how close they are with the target date (January 13, 2015 in this case).

I am unable to use conditional formatting as Excel says that relative formatting is not permitted within conditional formatting.

Target: 06-01-2015

01-01-2015
02-01-2015
03-01-2015
04-01-2015
05-01-2015
06-01-2015
07-01-2015
08-01-2015
09-01-2015
10-01-2015

If the date in the cell is more than 2 days of the target date, I want the cell to change color to Green. I used =DATEDIF($N12,$N$9,"d")>=3 and the format changed to green.

If the date in the cell is exactly 2 days near the target date, I want the cell to change color to yellow. I used =DATEDIF($N12,$N$9,"d")=2 and the format changed to yellow.

If the date in the cell is within 2 days of the target date, I want the cell to change color to red. I used =DATEDIF($N12,$N$9,"d")<2 and the format changed to red only for 5th and 6th January. Apparently, it does not recognize negative values.

How can I get this to match the required colors?

Best Answer

The problem using DATEDIF is it doesn't calculate negatives. Microsoft states:

Displays a #NUM! error because start_date occurs before end_date (#NUM!)

Therefore, the rule doesn't run as you entered it. Since Excel recognizes dates, use the simple function of SUM on this and it works as you described.

=SUM($N$9-N12)>=3 for green =SUM($N$9-N12)=2 for yellow =SUM($N$9-N12)<2 for red

Here are the rules.

enter image description here

And the results.

enter image description here

NOTE: My date format is m/d/yyy. This was done in 2010, but should be the same to 2013.

Related Question