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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Apply Conditional Formatting if Cell Contains Text
Excel: Apply Conditional Formatting with Multiple Conditions
Excel: Apply Conditional Formatting if Between Two Values
