1

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?

1
  • Other than the minor edits I made to improve your question, you did a great job explaining what you are trying to do. Only this that needs clarification is the date format. Are those dates dd/mm/yyyy?
    – CharlieRB
    Jan 8, 2015 at 14:23

1 Answer 1

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.

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.