Conditional Formatting Dates Overdue – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells with dates that are overdue (earlier than the current date) in Excel and Google Sheets.

 

 

Highlight Overdue Dates

Excel’s Conditional Formatting feature has many applications, one of them being that it can highlight dates before or after a certain date. You may want to do this to visually emphasize projects that are running late. Start with a list of projects and dates like the one below.

 

 

1. Select the range you want to apply formatting to.
2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

 

3. Select Use a formula to determine which cells to format, and enter the formula:

=C4<TODAY()

The TODAY Function returns the current date. That means the formula, and therefore formatting, is dynamic and will change depending on when the file is open.

4. Click Format.

 

 

5. For this example, the overdue dates should stand out, so set a red fill color as the format for those cells.

 

 

6. Click OK, then OK again to return to the Conditional Formatting Rules Manager.
7. Click Apply to apply the formatting to your selected range and click Close.

 

 

This formula entered will return TRUE when the due date is greater than today’s date and will format those cells accordingly.

 

 

Highlight Overdue Dates in Google Sheets

You can also use the TODAY Function in Google Sheets.

1. Highlight your range.
2. Type in the formula
3. Apply your Format Style.

= C4<TODAY()

 

 

4. Click Done.