Excel Conditional Formatting: Dates in the Past

To highlight dates before today in Excel for Microsoft 365, select the date cells and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For dates in A2:A100, enter =AND(ISNUMBER(A2),A2<TODAY()), choose a format, and confirm; the rule ignores blanks and highlights dates earlier than today.

What “a date in the past” means

The formula above treats “past” as any calendar date earlier than the current date. Yesterday and older dates qualify; today does not. That distinction matters in a deadline list: a due date of today is not technically overdue under this rule, even if the task is unfinished. If you want today’s dates flagged too, use a less-than-or-equal comparison instead, as described below.

Excel stores genuine dates as numbers, with a date format controlling how those numbers look in the cell. Conditional formatting evaluates the underlying value, not merely text that resembles a date. A date typed or imported as text may look correct on screen but fail a date comparison. The ISNUMBER check helps avoid highlighting blanks or text entries accidentally, but it also means text-formatted dates will not qualify until converted to real Excel dates.

Set up the rule in Excel

Select the date cells to format, such as A2:A100. Leave the heading out of the selection if it is in A1.
On the Home tab, choose Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter the formula shown above. Keep A2 as the reference if the top cell in the selected range is A2.
Select Format, choose the desired font, fill, border, or number format, and select OK.
Select OK again to create the rule. Excel applies it to the selected range.

For a range starting in another row, change the reference to its first cell. For example, if the selection begins at D5, the formula should refer to D5. Excel adjusts the relative row reference for each cell as it evaluates the rule. If you select a single date column, the basic reference is usually all that is needed.

On Excel for Mac, the rule is also available from Home > Conditional Formatting; the dialog labels and layout can differ slightly by release. In Excel for the web, select the range and use Home > Conditional Formatting to add a formula-based rule. The precise dialog controls may look different, but the formula logic is the same.

How the formula works

With dates in A2:A100 and the rule applied to that range, =AND(ISNUMBER(A2),A2<TODAY()) tests two conditions for each cell. ISNUMBER confirms that the cell contains a numeric value, which includes a genuine Excel date. The comparison checks whether that value is less than TODAY(), which returns the current date without a time component. AND requires both checks to be true.

For example, assume today is November 2, 2026, and the cells contain October 28, November 2, and November 5, 2026. The October date is highlighted, November 2 is not, and November 5 is not. When the date advances, the rule can begin highlighting November 2 as a past date after the workbook recalculates.

To include today as well as earlier dates, with the same A2:A100 layout, use =AND(ISNUMBER(A2),A2<=TODAY()). On November 2, 2026, that version highlights October 28 and November 2, but not November 5. Choose the comparison deliberately: a task tracker may define a deadline due today as active, while a report that marks all dates up to and including today may want the inclusive version.

Dates that include times

Excel date-and-time values contain a whole-number date portion and a fractional time portion. TODAY() returns today at midnight. As a result, the less-than-TODAY rule excludes every timestamp from today, even one earlier than the current time. This is generally right for a calendar-date deadline, but it is not the same as asking whether a timestamp has already passed.

If the cells contain timestamps and you want to flag any moment earlier than the current moment, use NOW(). With timestamps in A2:A100 and the rule applied to that range, enter =AND(ISNUMBER(A2),A2<NOW()). For instance, if the current time is 3:00 p.m., a timestamp from 2:00 p.m. today qualifies, while one from 4:00 p.m. does not. NOW() includes the current date and time, so it can produce a different result from TODAY() during the day.

Conversely, a timestamp earlier today should count only by its calendar date if the task is “before today.” With timestamps in A2:A100, use =AND(ISNUMBER(A2),INT(A2)<TODAY()). INT removes the fractional time portion for the comparison, so all timestamps from yesterday or earlier qualify and all timestamps from today do not. Use this only when the cells really contain numeric Excel date/time values.

Highlight an entire row based on its date

A common request is to color a complete task row when its due date has passed. Suppose each task occupies columns A through D, and its due date is in column C, with the first task on row 2. Select A2:D100, create a formula-based rule, and enter =AND(ISNUMBER($C2),$C2<TODAY()). The dollar sign fixes the date column at C while leaving the row number relative. Excel can then check column C for each row and apply the format across columns A through D.

Without the dollar sign before C, Excel would shift the tested column as it moves across the selected row. That can make the rule check a task name or status instead of the due date. The row number should generally remain unlocked: using $C$2 would make every selected row depend on the date in C2, which is not usually intended.

Built-in date rules and formula rules

Excel also offers date-oriented presets through Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Depending on the Excel release, the available choices include relative periods such as yesterday, today, tomorrow, and recent or upcoming weeks or months. These are convenient when one of the listed periods matches your need and you want a quick color rule.

A preset is not always equivalent to “any date before today.” A choice such as Yesterday targets a specific day, not every overdue date. For an open-ended past-date test, a formula rule is more suitable because you can define the boundary precisely, exclude blanks, handle timestamps, or apply the condition to entire rows. You can also use a preset for a quick visual check and later edit or remove it through Manage Rules.

Common problems and how to fix them

Nothing is highlighted: Check the Applies to range in Conditional Formatting > Manage Rules and confirm that it includes the intended cells. Also check that the dates are actual numeric dates, not text.
Blank cells are highlighted: Use the ISNUMBER test in the formula. A simple comparison without a type check can behave unexpectedly with empty or non-date cells.
The wrong row is colored: The formula reference should match the top-left cell or date column in the selected range. For whole-row formatting, lock the date column with a dollar sign, as in $C2.
Today is highlighted, but it should not be: Make sure the operator is < rather than <=. The latter includes today.
The rule does not update at midnight: TODAY() is a dynamic function, but the workbook must recalculate. Excel normally recalculates automatically; if calculation is set to Manual, press F9 on Windows or Fn+F9 on many Mac keyboards to recalculate. You can also check Formulas > Calculation Options.
Dates imported from another system behave oddly: Check for text values, unexpected day/month order, or timestamps. Convert text to genuine dates using an appropriate import or conversion method before relying on date comparisons.

Conditional formatting rules can also overlap. If another rule applies a fill or font format to the same cells, the visible result may depend on rule order and whether Stop If True is selected. Open Home > Conditional Formatting > Manage Rules, choose the relevant worksheet in the “Show formatting rules for” list, and inspect the Applies to range and rule order. The preview in the rule manager can help identify which formatting is being applied.

Keeping the rule useful

Choose a format that communicates status without making the sheet difficult to read. A pale fill or a distinct font color often keeps task names legible, while a strong color can be reserved for urgent items. If the workbook has several status rules, use consistent colors and labels so that readers do not have to infer what each shade means.

For shared workbooks, document the boundary clearly: for example, “red means due before today; today’s deadlines remain unflagged.” This prevents a reader from assuming that a date highlighted in the sheet is necessarily an unfinished task. Conditional formatting evaluates the date value only; it cannot determine whether the task is complete unless the rule also checks a status cell. A combined rule can use AND to test both the date and a status, but its references must match the table layout.

Dates do not age by changing the stored cell value. Instead, the formula is recalculated and the conditional format changes its appearance. This is useful for recurring dashboards, but it also means a workbook viewed on a different day can display a different set of highlighted dates. If a report must preserve the appearance as of a particular date, record that reporting date in a cell and base the rule on that fixed date rather than on TODAY().

Frequently Asked Questions

How do I highlight dates older than 30 days? With dates in A2:A100 and the rule applied to that range, use =AND(ISNUMBER(A2),A2<TODAY()-30). A date exactly 30 days before today is not highlighted by this strict comparison; use <= if you want to include that boundary date.

Can I highlight only overdue dates and ignore completed tasks? Yes. If dates are in C2:C100 and the corresponding status is in D2:D100, select the task rows and build a formula that checks both the due date and the status text. For example, =AND(ISNUMBER($C2),$C2<TODAY(),$D2<>"Complete") excludes rows whose status is Complete. Adjust the status text and column references to match the exact values in your sheet.

Why does Excel treat my date as text? Imported data may contain text strings, or the date order may not match the regional settings used during conversion. A date’s appearance alone does not prove it is numeric. Try changing the cell’s number format or use Excel’s data-conversion tools; if the value remains left-aligned or ISNUMBER returns FALSE, it may still be text.

Does the rule change the date in the cell? No. Conditional formatting changes only the cell’s appearance when its condition is true. It does not edit, delete, or replace the date value. You can remove or revise the rule through Conditional Formatting > Manage Rules without changing the underlying dates.

Can I use the same rule in Google Sheets? The general idea is similar, but the menus and rule setup differ. In Google Sheets, use Format > Conditional formatting and select a custom formula rule; do not assume that Excel’s dialog steps or rule manager are identical. The formulas and cell references should be checked for the layout and application in use.

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.