How to Automatically Highlight Overdue Dates in Microsoft Excel

Tracking deadlines, invoice payment schedules, and project deliverables in Microsoft Excel requires immediate visual alerts when commitments slip past their target dates. Relying on manual spreadsheet reviews or mental math to compare calendar dates is inefficient and invites human error. By implementing dynamic conditional formatting rules powered by the TODAY() function, Excel can automatically flag past-due dates in vivid soft reds or ambers the moment a deadline passes.

This tutorial walks you through setting up automated overdue date highlights, combining deadline calculations with task completion statuses (so completed items are not flagged incorrectly), and applying formatting to highlight entire table rows.

How Dynamic Date Comparison Works in Excel

Excel stores calendar dates as sequential serial numbers, where integer 1 represents 1 January 1900 and each subsequent day increments by one. Because dates are fundamentally numeric integers, you can perform direct mathematical comparisons between cell entries.

The core engine behind dynamic date evaluation is the TODAY() volatile function. Unlike static dates typed manually into cells, TODAY() continuously returns your computer’s current system date and recalculates every time you open or refresh the workbook. When a due date serial number is strictly less than TODAY(), the event sits in the past.

Method 1: Highlighting Overdue Cells Using Built-In Preset Rules

If your spreadsheet contains a standalone column of target dates and you solely need to highlight past dates without checking a completion status column, Excel offers a fast preset:

1. Open your workbook and highlight your range of due dates (for example, cells C2:C50).
2. Navigate to the Home tab on the ribbon.
3. In the Styles group, click Conditional Formatting.
4. Hover over Highlight Cells Rules and choose A Date Occurring….
5. In the left-hand dropdown, select Yesterday, or switch to the manual formula method below for comprehensive multi-day overdue coverage.

While the built-in preset accommodates recent dates, real-world task tracking requires evaluating any date older than today. For true automated monitoring, custom formulas provide complete flexibility.

Method 2: Highlighting All Past-Due Dates Using a Custom Formula

To highlight every date in your column that falls prior to today, follow these steps:

1. Select your due date range (e.g. C2:C50). Ensure you do not include the column header cell.
2. On the Home tab, click Conditional Formatting > New Rule.
3. In the New Formatting Rule dialogue, select the rule type: Use a formula to determine which cells to format.
4. Click inside the formula input box and enter:

   =AND(C2<>"", C2<TODAY())

   Note: The C2<>"" condition ensures that blank or unassigned date cells are not evaluated as zero (which Excel interprets as January 1900, mistakenly flagging empty rows as overdue).

5. Click the Format… button.
6. In the Format Cells dialogue, switch to the Fill tab and select a soft pastel red or coral swatch (e.g. Hex #FEE2E2).
7. Switch to the Font tab, select a dark red text colour (e.g. Hex #991B1B), and set the font style to Bold.
8. Click OK to close the format window, then click OK again to apply the rule.

Method 3: Excluding Completed Tasks with Multi-Condition Logic

In standard project management workflows, an overdue deadline is irrelevant if the work has already been marked as complete. If column C holds the due date and column D indicates task status (e.g. “Done” or “Completed”), you must instruct Excel to check both conditions simultaneously using the AND function.

1. Highlight your due date cells (C2:C50).
2. Go to Home > Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the following combined formula:

   =AND(C2<>"", C2<TODAY(), D2<>"Completed", D2<>"Done")

5. Click Format…, specify your warning fill colour, and click OK twice.

Now, whenever an employee updates column D from “In Progress” to “Completed”, the red highlight on the past-due date will instantly vanish, providing an accurate, clutter-free dashboard.

Method 4: Highlighting the Entire Table Row for Overdue Items

Highlighting only the date cell can sometimes be overlooked across wide spreadsheets. Formatting the entire row ensures that the overdue item captures immediate attention across all data columns.

1. Highlight your entire data table across all columns, excluding headers (for example, range A2:E50).
2. Click Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter your formula using an absolute column lock (the $ dollar prefix before the column letter):

   =AND($C2<>"", $C2<TODAY(), $D2<>"Completed")

   Crucial Concept: The $C2 syntax locks evaluation to column C while allowing the row number (2) to float freely down each record. This forces columns A, B, C, D, and E in row 2 to read the date in cell C2.

4. Click Format…, choose your background fill, and click OK twice.

Practical Formula Reference Guide

Scenario	Applied Formula (Assuming Due Date in C2)	Resulting Action
Basic Past Date	=AND(C2<>"", C2<TODAY())	Flags any non-empty date before today
Overdue & Incomplete	=AND(C2<>"", C2<TODAY(), D2<>"Done")	Ignores finished assignments
Due Today (Urgent)	=AND(C2<>"", C2=TODAY(), D2<>"Done")	Highlights immediate same-day deadlines
Due in Next 3 Days	=AND(C2>TODAY(), C2<=TODAY()+3, D2<>"Done")	Provides early warning amber indicator

Troubleshooting Common Date Formatting Errors

• Blank cells turn red automatically: If unpopulated rows are turning red, your formula is missing the C2<>"" blank-check condition. Update your rule formula to wrap the logic inside an AND(C2<>"", ...) wrapper.
• Dates formatted as text do not respond: If dates were imported from external web portals or CSV files as plain text strings (e.g. '15/11/2026), mathematical comparisons fail. Convert text dates to genuine numeric dates using the DATEVALUE function or by running the Text to Columns wizard on the column.
• Incorrect rows highlighting across the table: Ensure that the active cell at the moment you opened the Conditional Formatting dialogue was cell A2. If your selection started at A1 or A5 while your formula referenced $C2, Excel applies an unintended offset across all rows.