Conditionally Format Dates based on Different Criteria (>30, >60, >90...etc.)
For example, we have set of due dates and we would like to highlight dates more which are older than 30 days, 60 days, or 90 days.
Follow below steps:
1. Click on the first due date (cell B4)
2. Go to Conditional Formatting > New Rule
3. Add this formula and select appropriate formatting and hit OK
=(TODAY()-B4)<30 and select Green Background
4. Repeat steps 2 and 3 with below formulas and formatting
=(TODAY()-B4)>=30 (select Yellow Background)
=(TODAY()-B4)>=60 (select Orange Background)
=(TODAY()-B4)>=90 (select Red Background)
5. After that simply go to Conditional Formatting > Manage Rules > This Worksheet
and change the Applies to range accordingly. In this case I have changed it to B4:B10.
6. Click Apply and OK.
You should see below results.
You can use this simple logic to Conditionally format other data you have.
Hope this article was informative.
