Google Sheets: Apply Conditional Formatting to Overdue Dates


You can use the custom formula function in Google Sheets to apply conditional formatting to cells that are not equal to a specific value.

The following example shows how to use the custom formula function in practice.

Example: Apply Conditional Formatting to Overdue Dates in Google Sheets

Suppose we have the following dataset in Google Sheets that shows the due dates for various tasks at some company:

This article is being written on 6/27/2023.

Suppose we would like to apply conditional formatting to any date before today, e.g. 6/27/2023, since this is considered overdue.

To do so, we can highlight the cells in the range B2:B11, then click the Format tab, then click Conditional formatting:

In the Conditional format rules panel that appears on the right side of the screen, click the Format cells if dropdown, then choose Custom formula is, then type in the following formula:

=B2<TODAY()

Under the Formatting Style section, click the paint can icon and choose a fill color.

Once you click Done, each of the cells in the range B2:B11 that have a date before 6/27/2023 will be highlighted:

Google Sheets conditional formatting for overdue dates

Each of the cells filled with a light red color represent a date that is overdue.

Note: We chose to use a light red fill color, you change the conditional formatting to appear however you’d like by changing the settings in the Formatting style box.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: Conditional Formatting if Another Cell Contains Text
Google Sheets: Conditional Formatting if Another Cell is Not Empty
Google Sheets: Conditional Formatting from Another Sheet
Google Sheets: Conditional Formatting Based on Checkbox

Featured Posts

Leave a Reply

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