Table of Contents
In Excel, you can highlight overdue dates using conditional formatting. This feature allows you to set rules that will automatically apply formatting to cells based on their content. For example, you can set a rule that applies a particular color to cells that contain a date that is in the past. This makes it easy to quickly identify overdue dates in the spreadsheet.
To apply conditional formatting to cells that have an overdue date in Excel, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Apply Conditional Formatting to Overdue Dates in Excel
Suppose we have the following dataset in Excel that shows the due dates for various tasks at some company:
This article is being written on 1/16/2023.
Suppose we would like to apply conditional formatting to any date before today, e.g. 1/16/2023, since this is considered overdue.
To do so, we can highlight the cells in the range B2:B11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =B2<TODAY() in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the cells in the range B2:B11 that have a date before 1/16/2023 will be highlighted:
Note that the cell with a date equal to 1/16/2023 is not highlighted since it is not before the current date.
If you would like to consider the current date as overdue, simply type =B2<=TODAY() in the formatting rule box instead.
Note: We chose to use a light red fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.