Conditional Formatting Excel Dates Overdue: How to Set It Up

To highlight overdue dates in Excel, select the cells to format and create a formula-based conditional formatting rule. With due dates in C2:C100, use =AND(ISNUMBER($C2),$C2<TODAY()); it highlights valid dates earlier than today while ignoring blanks and text. Choose a fill color, and the rule will update as the date changes.

What counts as an overdue date?

In the usual task-list meaning, a date is overdue if it falls before today and the task is not finished. A due date equal to today is due today, not yet overdue. That distinction matters: a rule using a less-than comparison excludes today, while a less-than-or-equal comparison includes it.

Excel stores genuine dates as numbers, with whole-number values representing days and any decimal portion representing a time. Conditional formatting compares those underlying values, not the way they look on screen. For example, a cell displayed as 11/1/2026 can be compared with the current date even if the worksheet uses a different date display format.

In the opening example, suppose the computer’s current date is November 2, 2026. A valid due date of November 1 is earlier than today and is highlighted; November 2 is not. A blank cell is not treated as an overdue date because the rule checks that the cell contains a number first.

Create a rule for overdue dates

For a straightforward list with due dates in C2:C100, the formula assumes dates begin on row 2. The dollar sign fixes the column at C, while leaving the row number relative allows Excel to evaluate each row separately. The selected range and formula reference must line up: the first row of the Applies to range should be row 2.

Select the date cells, such as C2:C100. To color each entire task row instead, select the full table body, such as A2:D100.
On Windows, choose Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter the formula below, select Format, choose a fill or font style, and select OK. Select OK again to save the rule.

With due dates in column C and the first selected row at row 2, use:

=AND(ISNUMBER($C2),$C2<TODAY())

If you selected C2:C100, only overdue date cells receive the formatting. If you selected A2:D100, the same formula can highlight the whole row based on its date in column C. In the latter case, the dollar sign before C is important: it keeps every cell in a row tied to that row’s due date, rather than shifting the date reference across columns.

In Excel for Mac, the Conditional Formatting controls are on the Home tab and the rule dialog offers a formula-based option; wording and dialog layout can differ slightly by release. Excel for the web can display and edit many conditional formatting rules, but if you cannot create or adjust the specific formula rule you need, open the workbook in desktop Excel.

Exclude completed tasks

A date can be in the past even when its task is done. If column C contains due dates and column D contains task status, use a rule that also checks status. Assume the first task is in row 2, completed tasks are labeled Complete, and the selected range begins on row 2:

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

This highlights past-due dates only when the status is not Complete. The text comparison is not case-sensitive in a standard Excel comparison, so Complete and complete are treated alike. If your workbook uses a different status label, such as Done, change the text in the formula to match it exactly. If an empty status should not be treated as an unfinished task, use this version instead, with dates in C and statuses in D starting at row 2:

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

These rules do not alter any cell values; they only change how matching cells appear. That makes conditional formatting useful for a live work list, but it does not mark tasks complete, send a reminder, or sort overdue items automatically.

Choose the right definition of overdue

Teams do not always use the same cutoff. Decide whether something due today should be red before building the rule. With due dates in C2:C100 and the selected range starting at row 2, this version treats today as overdue as well as earlier dates:

=AND(ISNUMBER($C2),$C2<=TODAY())

Use the strict less-than rule when today is still an acceptable due date. Use the less-than-or-equal rule when the warning should appear on the due date itself. For an operational dashboard, a third category can be helpful: one color for past-due items, another for items due today, and a neutral style for later dates. Separate rules make those categories easier to read and adjust.

If deadlines include times, TODAY is not precise enough for deciding whether a deadline earlier today has passed: it represents the current date at midnight. With date-and-time deadlines in C2:C100, use the current date and time instead, assuming the selected range starts at row 2:

=AND(ISNUMBER($C2),$C2<NOW())

This compares each deadline with the current time. A date-only entry is treated as midnight, so it will be earlier than the current time for most of that day. Do not use this version if a date without a time is intended to remain valid through the end of its due date.

Understand TODAY and recalculation

The date-based rules above use Excel’s TODAY function, which returns the current date according to the computer’s date settings. Because the result can change, the formatting is dynamic: a date that is not overdue today can become overdue after the day changes, without editing the cell.

Excel recalculates formulas as the workbook is edited and opened. If a rule appears not to update, check the workbook’s calculation setting under Formulas > Calculation Options and make sure calculation is set to Automatic. In Manual mode, a recalculation may be needed before formula results and formatting reflect the current date. Saving and reopening can also prompt a refresh, but it is better to verify the calculation setting rather than rely on that workaround.

The rule is based on the date recognized by Excel, not a date typed into a separate report or the time zone of a remote colleague. If a workbook is shared across locations, teams should agree on the date and time convention for deadlines. For a standard date-only task list, the computer’s local calendar date is generally the intended reference.

Fix common problems

If overdue cells are not highlighted, first confirm that the entries are real Excel dates. Imported dates sometimes arrive as text. They may look like dates but fail the numeric check in the rule. Try changing the cell’s number format to General: a genuine date typically displays as a number, while text remains text. Re-entering a date, using Text to Columns, or converting the source data may be necessary, depending on how it was imported.

Next, check the Applies to range and the formula’s starting row. If the selected range starts at row 5 but the formula refers to C2, Excel evaluates the wrong row relationship. For a range beginning at A2, a reference such as $C2 is appropriate; for a range beginning at A5, use $C5. Keep the column fixed when each row’s due date is in column C, but do not lock the row number.

Also check for an existing rule with a conflicting format. Choose Home > Conditional Formatting > Manage Rules to review the rules applied to the current selection or worksheet. A later rule may apply a different fill, and rule order or Stop If True can affect which formatting is visible. Clear obsolete rules or reorder them carefully rather than adding repeated rules until one appears to work.

Everything is red: verify that the formula is comparing the intended date column and that the selected range begins on the referenced row.
Blank cells are red: use a rule with the ISNUMBER check rather than a bare date comparison.
Dates that look old are ignored: check whether they are stored as text, or whether the workbook is using Manual calculation.
The wrong rows are highlighted: correct the formula’s row number and review the Applies to range.

Use clear formats and maintain the rule

Red fill is a common warning, but color should not be the only signal in a busy or shared workbook. A contrasting font style, a text status column, or an accessible color choice can make the result easier to interpret. Keep the format visible without making the sheet difficult to scan; a pale fill often preserves readability better than a saturated background.

When a task list grows, extend the Applies to range so new rows receive the rule. If the data is formatted as an Excel Table, table rows commonly expand as new records are added, but still review the rule’s range after inserting or pasting data. Copying cells from elsewhere can bring additional conditional formatting rules into the worksheet, so check Manage Rules when the formatting becomes inconsistent.

Conditional formatting is a visual alert, not a permanent record of when a task became late. If you need an audit trail, notifications, or automatic follow-up, use a process designed for that purpose, such as a maintained status-and-date log or a workflow tool. A conditional format is best for quickly spotting dates in the current sheet.

Frequently Asked Questions

How do I highlight dates overdue in Excel but ignore blanks?
Use a formula rule that checks both that the due-date cell is numeric and that it is earlier than today. For due dates in column C with the first data row at row 2, use =AND(ISNUMBER($C2),$C2<TODAY()).

How do I highlight an entire row when its date is overdue?
Select the table rows you want to format, for example A2:D100, and base the rule on the date column with a fixed column and relative row. If dates are in C and the first row is 2, use =AND(ISNUMBER($C2),$C2<TODAY()). The rule then evaluates the date in column C for each row.

Can Excel highlight a date that is due today?
Yes. Use a less-than-or-equal comparison instead of a strict less-than comparison. With dates in C and the first data row at row 2, =AND(ISNUMBER($C2),$C2<=TODAY()) includes today and all earlier dates.

Why does my conditional formatting rule not update tomorrow?
Check Formulas > Calculation Options and select Automatic. The date function updates through recalculation, so Manual calculation can leave the displayed result stale until Excel recalculates the workbook.

Does this work if my dates are text?
Not reliably. The rule checks for numeric date values, so text that merely looks like a date will not qualify. Convert the imported values to actual Excel dates, then verify the date display and the rule again.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.