Highlight Dates Over 30 Days Old

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.

Once you are familiar with how to compare dates in Excel and highlight the dates that meet specific criteria, you can adjust the formula used in this tutorial to meet your needs. For example, to highlight overdue dates, or those due within the next 7 days.

Create the Rule

1. Select the cells containing the dates you want to format
2. Click the Conditional Formatting button on the Home tab of the Ribbon and select New Rule from the list
3. Select Format only cells that contain as the rule type
4. Choose Cell Value from the first list and less than from the second list in the rule description
5. 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

6. Click the Format button and choose the formatting you wish to apply
7. Click Ok

All dates older than 30 days are highlighted with the formatting you chose.

Conditional Formatting is an extremely useful feature of Excel.