Excel: Apply Conditional Formatting to Overdue Dates


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.

Excel conditional formatting for overdue dates

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

One Reply to “Excel: Apply Conditional Formatting to Overdue Dates”

Leave a Reply

Your email address will not be published. Required fields are marked *