How do I use Conditional Formatting based on due dates in Excel 2013?
Solution 1:
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.
And the results.
NOTE: My date format is m/d/yyy. This was done in 2010, but should be the same to 2013.