I was asked yesterday how to highlight dates that were over 30 days old. The situation was that the date of a transaction is recorded and the team need to be alerted when that date is older than 30 days.
Because they wish to highlight the cell, Conditional Formatting will be used. A new rule will be created with criteria to find those dates older than 30 days.
Create the Rule
- Select the cells containing the dates you want to format
- Click the Conditional Formatting button on the Home tab of the Ribbon and select New Rule from the list
- Select Format only cells that contain as the rule type
- Choose Cell Value from the first list and less than from the second list in the rule description
- Enter the formula =today()-30 in the next box. This formula uses the Today function to find the current date and then subtracts 30 days from it
- Click the Format button and choose the formatting you wish to apply
- Click Ok
All dates older than 30 days are highlighted with the formatting you chose.
Conditional Formatting is an extremely useful feature of Excel. Check out more Conditional Formatting tutorials below.