Conditional Formatting: Highlight Dates in the Past

Yes. In Excel for Microsoft 365, select the date cells, choose Home > Conditional Formatting > New Rule, and use a formula such as =AND(A2<>"",A2<TODAY()). Set the format you want; the rule highlights dates earlier than today and leaves blank cells unhighlighted.

What “past” means in a date rule

A past date is earlier than the current date. The formula uses TODAY() to get today’s date, then compares each cell’s value with it. A date from yesterday or last year meets the condition; today’s date does not. That distinction matters for due dates: if a task is due today, it is not yet overdue under this rule.

Conditional formatting changes how a cell looks, not its contents. Excel keeps the original date and applies a fill, font color, border, or other format while the condition is true. Because the rule refers to today rather than a fixed date, its result can change as days pass without anyone editing the date cells.

Excel stores real dates as numeric values, so date comparisons work even if cells display dates in different formats, such as 11/2/2026 or Nov 2, 2026. The display format does not determine whether a date is in the past; the underlying value does.

Create a rule for past dates

For a list of dates in A2:A100, select A2:A100 before creating the rule. The formula assumes A2 is the first cell in that selected range; Excel adjusts the row reference for each cell. The example formula returns TRUE for a nonblank date before today, and FALSE for a blank cell, today, or a future date.

Select the date range, for example A2:A100.
On the ribbon, choose Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter =AND(A2<>"",A2<TODAY()).
Select Format, choose a fill or other appearance, then select OK to save the format and the rule.

The first reference, A2, should match the top-left cell of the selected range. If your selection begins at C5, use C5 in the formula instead. Keep the column and row references relative when formatting individual date cells; this lets the rule evaluate each cell in turn.

The blank check is useful because an empty cell can behave like zero in a numeric comparison, and zero is earlier than today’s date serial. Without the check, empty cells in the range may be highlighted as if they were old dates. The check also avoids highlighting cells whose formula returns an empty string.

Highlight a whole row based on its date

You can use a date in one column to highlight the rest of its row, which is handy for task lists, invoices, and project trackers. Suppose due dates are in column B and the table occupies A2:F100. Select A2:F100, create a formula-based rule, and use the formula below. It checks the date in column B for each row; the dollar sign locks the column while the row number changes. For a row with yesterday’s date in B2, the condition is TRUE and the row is formatted; for a blank or future date, it is FALSE.

With due dates in B2:B100 and the formatting range A2:F100, enter =AND($B2<>"",$B2<TODAY()). Choose the desired format and save the rule. Make sure the formula’s row number, 2 here, matches the top row of the selected range. If the selected range starts on row 5, the reference should start with $B5.

To exclude completed work as well, assume due dates are in B2:B100, statuses are in C2:C100, and the rule applies to A2:F100. This formula returns TRUE only when the row has a past due date and its status is not Complete. For example, a row dated yesterday with Open in C2 is formatted; a row with Complete is not.

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

Choose the right meaning of overdue

Decide whether “past” means strictly before today or includes today. The first rule treats today as current, which suits many overdue-date lists. If a deadline should be flagged on its due date as well as afterward, use a less-than-or-equal comparison instead. With dates in A2:A100 and A2 as the first cell in the selected range, the formula below returns TRUE for dates through today, including today; blank cells remain excluded.

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

That variation is not universally better. For example, a payment due today may not be considered late until the next day, while an event scheduled for today may need to stand out immediately. Agree on the intended business meaning before applying one rule to a shared workbook.

Also consider whether your cells contain times as well as dates. A value can include both a date and a time. Comparing it with TODAY() still distinguishes earlier calendar dates from today, but a timestamp later today is greater than today’s midnight value. If you need to flag items that are late by the current time, use a time-aware rule based on NOW() and define the deadline behavior carefully.

Use the built-in date presets or a formula?

Excel also offers date-based conditional formatting presets. For a quick, limited rule, select the cells and look under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. The available choices cover familiar periods such as yesterday, today, tomorrow, and recent or previous periods. The exact preset is useful when it matches your need and you do not need to customize the boundary.

A formula rule is more flexible when you want every date before today, want to exclude blanks, need to shade an entire row, or need another condition such as status. Presets are quicker to set up, but they may not express the precise rule you have in mind. You can inspect or change saved rules through Home > Conditional Formatting > Manage Rules.

Another option is to sort or filter a table by date. Filtering can show only past dates without changing the cell appearance, while conditional formatting keeps overdue items visually marked in the full list. They solve different problems and can be used together.

Common problems and how to fix them

Blank cells are highlighted: Add the nonblank test shown in the examples, and check whether apparently blank cells contain formulas returning an empty string.
Nothing is highlighted: Confirm that the entries are real Excel dates rather than text that merely looks like dates. Try changing a cell’s number format to a date or checking its value; if necessary, convert the text to dates before relying on comparisons.
The wrong rows are highlighted: Check the formula’s first reference against the top-left cell of Applies to. A formula starting with A2 is offset if the range starts at A3. For row-wide formatting, lock the date column, as in $B2, but normally leave the row number unlocked.
Today’s date is highlighted unexpectedly: Check whether you used <= instead of <, or whether another conditional-formatting rule is adding a format.
The highlight does not change after midnight: TODAY() updates when Excel recalculates the workbook, not as a continuously running clock. Check that calculation is set to Automatic under Formulas > Calculation Options, then recalculate or reopen the workbook if needed.
Dates appear to be in the past when they are not: Check the year, regional date interpretation, and whether imported values were converted correctly. A text value can be misread during conversion, and a genuine date with an unexpected year will compare exactly as stored.

Conditional-formatting rules can overlap. If another rule applies a fill or font format to the same cells, the visible result may depend on rule order and which format settings each rule changes. Open Manage Rules, choose the relevant worksheet in the display list, and review the rules and Applies to ranges. Editing or removing an old rule may be clearer than adding extra rules that compete.

Keeping the rule reliable in a changing list

If rows are added regularly, consider formatting an Excel table rather than a fixed range. Table columns can expand as records are added, but verify that the conditional-formatting rule’s Applies to range includes new rows. When you paste data, check that the date values and rule behavior are preserved; pasting formats from another source can create unexpected formatting or additional rules.

For a shared workbook, use a clear visual style and keep the meaning consistent. A pale fill may signal attention without making text hard to read, while a strong red fill can imply urgency. Do not rely on color alone if readers may print in grayscale or have difficulty distinguishing colors; a status column or written label provides an additional cue.

The date rule is dynamic, but it does not change the date or mark a record as processed. If you need a permanent record of when an item became overdue, conditional formatting alone is not an audit trail. Store that event explicitly using an appropriate workflow rather than expecting a changing visual rule to preserve history.

Frequently Asked Questions

Does conditional formatting update automatically when the date changes?
Yes, the rule is evaluated when Excel recalculates. TODAY() supplies the current date, but it is not a live timer; automatic calculation is usually enabled by default. If the workbook is in manual calculation mode, recalculate it to refresh the result.

How do I highlight dates before today but not blank cells?
Use a formula rule that tests both conditions. With dates in A2:A100 and A2 as the first selected cell, use =AND(A2<>"",A2<TODAY()). This excludes blanks and excludes today itself.

Can I highlight the whole row when a date is overdue?
Yes. Select the rows or table range you want to format, then refer to the date column with a locked column and an unlocked row. For due dates in B and a selected range beginning on row 2, use =AND($B2<>"",$B2<TODAY()).

Why is Excel highlighting dates that look like text?
Formatting a text string to look like a date does not necessarily convert it into a real date value. Convert imported text dates into Excel dates, taking care with regional day-and-month order, then recheck the conditional-formatting rule.

Can I highlight a date that is past due including today?
Yes. Use a less-than-or-equal comparison rather than a strict less-than comparison. Add a nonblank test so empty cells are not treated as old dates, and decide whether flagging something due today fits your workflow.

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.