Conditional format entire row based on overdue date range

**jtrocki001** · 05-31-2013, 10:41 AM

Question: In Microsoft Excel 2010, is there a way to automatically highlight past due dates bases on a range of days?

For example, I have a column with a lists of dates depicting when milestones are due. I would like Excel to highlight dates using conditional formatting based on how long they are overdue. I would like to highlight milestones over 60 to 90 days overdue in yellow, Actions 90 to 120 days overdue in orange and Actions over 120 days overdue in red. Based on todays date.

I have created the following formula for the Actions over 120 days overdue in red:

=AND(F1<>"",F1<TODAY()-121)

It works fine.

I just cant get the range to work properly. i.e. milestones over 60 to 90 days overdue or Actions 90 to 120 days overdue . This is the formula that I was working on and cant get to work.

=AND(F1-TODAY()>=-60,F1-TODAY()<=-90)
=AND(F1<>"",F1<TODAY()<=-120,F1<>"",F1<TODAY()>=-90)

Any assistance is greatly appreciated.

**Cheeky Charlie** · 05-31-2013, 11:13 AM

Corrected versions of your formulae:
=AND(F1<>"",F1-TODAY()<=-60,F1-TODAY()>=-90)
=AND(F1<>"",F1-TODAY()>=-120,F1-TODAY()<=-90)

But, the simpler approach is to reuse the first formula:
=AND(F1<>"",F1<TODAY()-121)
...
=AND(F1<>"",F1<TODAY()-90)
=AND(F1<>"",F1<TODAY()-60)

With the rules applied in that order (red at the top). This will achieve the same effect and is easier to understand. Basically, everything older than 60 is coloured yellow, then amber is applied to everything over 90, which re-colours some of the red).

**wesleypeter** · 05-31-2013, 11:32 AM

You can get your work done without using formula. Using Excel 2010, you may use the Conditional Formating feature to get the desired result.

Find the result in the attachment. To know the usage of Conditional Formating, under Home tab, Select Conditional Formating -> Manage Rules.