Conditionally Format Dates based on Different Criteria (>30, >60, >90...etc.)
- Ketul Patel
- Jul 27, 2016
- 1 min read
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.
