Conditional Formatting for Overdue Dates in Excel: Guide

In Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND($A2<>"",$A2<TODAY()) if dates begin in A2. Choose a format and apply the rule to your date range; this highlights dates before today while leaving blank cells and dates due today unmarked.

What an overdue-date rule does

Conditional formatting changes a cell’s appearance when a condition is true. For an overdue-date list, that condition is usually that a real date value is earlier than today. Excel stores dates as serial numbers, so comparing a cell with TODAY() lets the rule make the comparison without needing to update the date in the rule each morning.

The exact meaning of “overdue” is a choice for your workflow. Some lists consider an item overdue only after its due date has passed, so a deadline of today is not highlighted until tomorrow. Other teams consider anything due today to need attention and want it highlighted throughout the day. Decide which interpretation fits your process before building the rule; the two rules differ at the boundary.

Formatting is a visual signal, not a change to the underlying date or a notification. The rule does not mark a task complete, send an alert, or change the sort order. It can, however, make a long list easier to scan and help people spot past deadlines quickly.

Set up a basic rule in Excel

Suppose due dates occupy A2:A100, with a heading in A1. To highlight only overdue dates, use a formula-based rule:

Select A2:A100. Start at the first data row, not the heading.
On the Home tab, choose Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter the formula shown below, then select Format to choose a fill, font, or border.
Choose OK to save the format and OK again to create the rule.

With due dates in A2:A100 and the rule applied to that same range, the formula assumes its first row is row 2. If A2 contains October 31, 2026, and today is November 2, 2026, the condition is true and A2 is highlighted. If A3 contains November 2, it is false; if A4 is blank, it is also false.

=AND($A2<>"",$A2<TODAY())

The dollar sign fixes the column A while leaving the row relative. Excel therefore checks A2 for the first formatted cell, A3 for the next row, and so on. If your dates are in column C and the first data row is 5, select the intended range beginning at C5 and adjust the column and row references accordingly.

Choose whether today counts as overdue

The basic rule uses a strict “earlier than today” comparison. A due date of November 2 is not earlier than November 2, so it stays unformatted on that date. This is useful when a task is still on time until the day ends. When the workbook recalculates the next day, that same date becomes earlier than today and meets the condition.

If your policy treats a deadline dated today as overdue or needing immediate attention, use an inclusive-through-today comparison instead. With due dates in A2:A100, this version returns TRUE for an October 31 date and for a November 2 date when today is November 2; it returns FALSE for a blank cell and for a November 3 date.

=AND($A2<>"",$A2<TODAY()+1)

This rule considers every date on the current calendar day to qualify. It is not the same as checking whether a specific deadline time has passed. If a cell holds a date and time, choose a rule that matches whether your policy is based on the calendar date or the exact time.

Apply formatting to an entire row

Often the useful signal is not a colored date cell but a highlighted task row containing its owner, description, and due date. For example, if dates are in column A and records occupy A2:D100, select A2:D100 before creating the rule. Keep the date column fixed in the formula but leave the row relative.

With due dates in A2:A100 and the rule applied to A2:D100, the formula below checks column A for each row. If A2 contains October 31, 2026, and today is November 2, the formatting condition is true for cells across row 2. A blank in A3 produces no highlight for row 3.

=AND($A2<>"",$A2<TODAY())

In the New Formatting Rule dialog, the “Applies to” range should cover the rows and columns you want formatted, such as A2:D100. The rule formula still refers to the first row of that range. A common mistake is to select A2:D100 but use a formula referring to A1; Excel then evaluates the wrong row and the colors appear shifted.

Exclude completed tasks or add other conditions

A past due date may not need attention if the task is already complete. If column A contains due dates and column B contains status text, a formula can require both a past date and a status other than Complete. Use the same range and first-row alignment principles as for the basic rule.

With due dates in A2:A100, statuses in B2:B100, and the rule applied to A2:D100, the formula below highlights a row when its date is earlier than today and its status is not exactly Complete. For example, an October 31 date with “In progress” in B2 qualifies on November 2; the same date with “Complete” does not. A blank date also does not qualify.

=AND($A2<>"",$A2<TODAY(),$B2<>"Complete")

Text comparisons such as this are not a substitute for consistent status values. Extra spaces, spelling differences, or labels such as “Completed” can make the result differ from what you intended. Use a consistent list of status choices, and make sure the formula checks the exact wording used in the workbook. If your status column has a different location, change the column reference while preserving the appropriate row reference.

Dates, times, and automatic updates

TODAY() returns the current date according to the computer’s date setting, without a time component. Excel updates volatile functions such as TODAY during recalculation, which normally happens when the workbook opens or when calculations are triggered. If a workbook is set to manual calculation, a rule may not refresh as expected until a recalculation occurs. On Windows, Formulas > Calculation Options lets you check whether calculation is Automatic.

A date-time value contains a fractional time as well as its date. Comparing it with TODAY means the value for today at midnight is not earlier than TODAY, but a timestamp from yesterday is. When the intended rule is “the timestamp is earlier than this moment,” use NOW instead. With timestamps in A2:A100 and a rule applied to that range, if the current time is November 2, 2026 at noon and A2 contains November 2 at 11:30 a.m., this condition is true; a timestamp at 12:30 p.m. is false.

=AND($A2<>"",$A2<NOW())

NOW also depends on recalculation, and it compares the actual stored date-time values, not merely the way cells are displayed. A cell formatted to show only a date can still contain a time that affects the comparison. If times are not meaningful in your process, keep the data as date-only values or use a calendar-date rule rather than relying on hidden time portions.

Check the rule when it does not work

If the expected cells are not highlighted, inspect the rule and the data rather than changing colors first. Conditional formatting needs a valid date value and a correctly aligned formula. A date that looks right on screen may be text, particularly if it was imported or pasted from another system.

Check the date value. Change the cell’s number format temporarily to a date or a number, and verify it behaves like a date in Excel. Text dates may need to be converted before a comparison works reliably.
Check “Applies to.” Go to Home > Conditional Formatting > Manage Rules. Confirm the range starts on the same row referenced by the formula and includes the cells or rows intended.
Check reference locks. For a row-based rule, a fixed date column such as $A and a relative row such as 2 are usually appropriate. Locking the row as $2 can make every row evaluate against the same cell.
Check blanks and formulas returning empty text. The nonblank test avoids highlighting empty cells. If the date cell contains a formula that returns an empty string, the same test ordinarily keeps it from being treated as a due date.
Check other rules. In Manage Rules, look for overlapping rules with competing formats or an unexpected “Applies to” range. Adjust their order or conditions when needed.
Check calculation settings. If TODAY has not advanced, confirm calculation is Automatic or trigger a recalculation.

Regional settings can affect how typed dates are interpreted. For example, a string such as 04/05/2026 can mean different calendar dates in different locales. Excel may display valid date values according to the local format, but imported text can be especially ambiguous. When accuracy matters, use unambiguous date entry, confirm the displayed month and day, and avoid assuming that a particular slash format means the same thing on every computer.

Manage the rule and choose a useful visual format

Use a format that draws attention without making the sheet difficult to read. A pale red fill with dark text is a common overdue signal, but it is not the only option. A colored font, a border, or an icon can work depending on the workbook’s design. Do not rely on color alone when the sheet is shared with people who may have difficulty distinguishing colors; a status label or a clear heading can provide additional context.

To edit or remove a rule, select a cell in the affected range and open Home > Conditional Formatting > Manage Rules. In the Rules Manager, inspect the rule, formula, and “Applies to” box. The “Show formatting rules for” menu can display rules for the current selection or the worksheet, which helps when a rule seems to have disappeared because the wrong scope is being viewed.

When copying a worksheet or extending a list, verify that the new rows are included in the rule’s “Applies to” range. Excel tables can expand as rows are added, but do not assume a rule has expanded correctly in every workbook setup. Check the range after adding records, especially if the table was converted to a normal range or the rule was created on only a fixed selection.

Excel for Mac and Excel for the web

The core formula approach also works in current Excel for Mac and Excel for the web, though dialog layouts and some controls may look different. On Mac, select the range and use Home > Conditional Formatting to create or manage a rule; the exact dialog wording can vary by build. In the browser, conditional formatting is available from Home > Conditional Formatting, and formula-based rule options depend on the available interface and workbook context.

These examples use commas between formula arguments, as required by the stated Windows setup. Some regional configurations use semicolons as formula separators. If Excel rejects a formula even though its logic is sound, check the list separator configured for that installation rather than changing the date comparison itself. The menu path can also vary slightly as Microsoft updates Office, but the rule type to look for is a formula-based conditional-formatting rule.

Frequently Asked Questions

How do I highlight overdue dates but ignore blank cells?
Use a formula-based rule with a nonblank check, such as the basic rule shown above. The check matters because Excel can treat a blank cell like zero in a date comparison, which would otherwise make it appear older than today.

Does an overdue rule update automatically each day?
It updates when Excel recalculates the workbook. With calculation set to Automatic, TODAY normally advances as the date changes and the conditional formatting is reevaluated. Manual calculation can delay that update until recalculation occurs.

Can I highlight the whole row based on a date in one column?
Yes. Apply the rule to the full row range, use a fixed reference to the date column, and keep the row reference relative. For dates in column A beginning in row 2, a rule applied to A2:D100 should refer to $A2.

Should a deadline due today count as overdue?
That depends on the policy you want the sheet to represent. The less-than-TODAY rule flags dates only after today has passed; a comparison against TODAY()+1 includes all of today. For deadlines that include a precise time, compare date-time values with NOW when appropriate.

Why is a date not highlighted even though it looks overdue?
The cell may contain text instead of an Excel date, the rule may point to the wrong row or range, or another rule may affect the appearance. Also verify that calculation has refreshed TODAY and that the date was interpreted correctly under the computer’s regional settings.

Can conditional formatting send an overdue reminder?
No. Conditional formatting changes how cells look; it does not send email, notifications, or reminders. If reminders are needed, use an appropriate workflow or notification tool and treat the spreadsheet’s formatting as a visual aid rather than an alerting system.

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.