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.