To highlight dates before today in Excel for Microsoft 365 on Windows, select the date cells and create a conditional-formatting rule with =A2<TODAY(), replacing A2 with the first cell in your selected range. The rule highlights dates earlier than the current calendar day and updates as the date changes; use an extra check for blanks or text if those are present.
What the rule does
Excel stores dates as serial numbers, so a comparison such as =A2<TODAY() compares the value in A2 with the serial number for the current date. The less-than sign means strictly earlier: yesterday and older dates qualify, but today does not. This is useful for overdue deadlines, expired dates, past appointments, and records that need review.
TODAY() returns the current date without a time. It is a live function rather than a fixed date typed into the rule. As Excel recalculates the workbook on a later day, the comparison uses the new date. The conditional format changes the cell’s appearance; it does not alter the date, delete a record, or mark it as permanently overdue.
Set up the conditional-formatting rule
For example, suppose dates are in A2:A100. Select that range, then create a formula-based rule. The formula must refer to the first selected cell, and the row and column references should usually remain relative so Excel can evaluate each cell separately.
- Select A2:A100, or select the actual date range in your worksheet.
- On the Home tab, choose Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=A2<TODAY(), then choose Format and set a fill, font, or border. - Choose OK to save the rule, and confirm the range in the rule manager if needed.
The formula is written as if it is checking A2 because that is the top-left cell of the selected range. Excel adjusts the reference for the other cells: it checks A3 for the next row, A4 for the following row, and so on. If your range begins at D5, use =D5<TODAY() instead. In Excel for Mac, conditional formatting is also available from the Home tab; the exact dialog layout can vary slightly by release.
Exclude blank cells and non-date values
A plain comparison is concise, but a column may contain blanks, notes, or values that are not real Excel dates. To require a nonblank value, with dates in A2:A100 and the rule applied to that range, use =AND(A2<>"",A2<TODAY()). This avoids coloring empty cells in ordinary date columns.
If the column may contain text, a stronger check is to require a numeric value. With the same range and starting cell, use =AND(ISNUMBER(A2),A2<TODAY()). Excel dates are numeric serial values, so this rule ignores text labels as well as blanks. It also ignores numbers that are not intended as dates, however; keeping the column’s contents consistent is still important. If text dates were imported from another system, convert them to real Excel dates before relying on comparisons.
Conditional formatting uses the formula’s TRUE or FALSE result. The extra conditions do not change the date threshold; they simply limit which cells the rule evaluates as eligible. Choose the simplest rule that fits the data rather than adding checks that your worksheet does not need.
Apply the rule to an entire row
You can highlight a whole record when its date in one column is in the past. Suppose each record occupies columns A through F, dates are in column C, and the first record is on row 2. Select A2:F100 and use =$C2<TODAY(). The dollar sign fixes the date column at C as Excel evaluates cells across each row, while the row number remains relative so each record is checked against its own date.
For a row-level rule that should ignore empty or text entries in column C, use =AND(ISNUMBER($C2),$C2<TODAY()). Check that the “Applies to” range starts on the same row referenced in the formula. If the range starts on row 3 but the formula uses row 2, the highlighted records can be offset by one row.
Choose the right date boundary
“Before today” normally means strictly earlier than today, which is why the less-than operator is appropriate. If today should also count, a simple date-only comparison is <=TODAY(). For a date column that can include times, be careful: TODAY() represents midnight at the start of the current day. A timestamp from this afternoon is greater than TODAY(), even though its calendar date is today.
To include every time on today’s date as well as earlier dates, compare against tomorrow’s midnight instead. With dates or date-and-time values in A2:A100, use =A2<TODAY()+1. Add the nonblank or ISNUMBER condition if needed. This boundary includes a timestamp at any time today but excludes a timestamp tomorrow or later. For a rule that should identify only dates strictly before the current calendar day, retain <TODAY().
These distinctions matter in schedules and logs. A cell displayed as “Nov 3” might contain a time that is hidden by its number format. The displayed date alone may not reveal why a comparison behaves differently. You can inspect or change the number format, but formatting a value as a date does not turn text into a real date.
Dates, text, and regional formats
Excel recognizes valid dates as numbers, even though it displays them in familiar forms such as 11/3/2026 or 3-Nov-2026. The underlying serial value makes date arithmetic and comparisons possible. Different number formats can show the same stored date differently without changing what the rule compares.
Imported data is a common source of confusion. A value that looks like a date may actually be text, especially if it came from a CSV file or another system using a different regional date order. For example, a text string such as “03/11/2026” may be ambiguous between March 11 and November 3 depending on locale. Check the source format and convert the text deliberately rather than assuming that a visual date is a usable date value.
When an expected cell is not highlighted, test whether Excel recognizes its value as a number. With the date in A2, =ISNUMBER(A2) returns TRUE for a numeric Excel date and FALSE for text. This is a diagnostic formula for a worksheet cell, not the conditional-formatting rule itself. Conversion options include Excel’s date conversion tools or a careful import step; verify the interpreted month and day after conversion.
Review, edit, and troubleshoot rules
To inspect a rule, select a cell in the affected range and go to Home > Conditional Formatting > Manage Rules. The rule manager lets you review the formula, formatting, and “Applies to” range. If the condition is correct but the wrong cells change, check whether the range begins where the formula expects it to begin and whether any references have been accidentally locked with dollar signs.
- Nothing is highlighted: confirm that the cells contain real numeric dates, that the formula refers to the correct column, and that the rule applies to the intended range.
- Every row appears highlighted: look for a reference that is fixed incorrectly, such as
=$A$2<TODAY(), which checks the same cell for every position. - Blank cells are colored: add a nonblank condition or use the ISNUMBER version, depending on the contents.
- The rule seems out of date: Excel normally recalculates TODAY() automatically, but a workbook using manual calculation may not update until recalculation. Check Formulas > Calculation Options and recalculate if necessary.
- A date from today is highlighted unexpectedly: inspect whether the cell contains a time or whether the rule uses a different boundary, such as TODAY()+1.
Multiple conditional-formatting rules can apply to the same cells. In Manage Rules, check their order and whether a rule has Stop If True enabled, since that setting can prevent later rules from being applied. If the formatting remains confusing, temporarily simplify the range and use one rule to verify the formula before rebuilding the full set of conditions.
Conditional formatting versus a helper formula
Conditional formatting is best when the goal is visual: highlight overdue dates without adding another column or changing the data. A helper formula is useful when you need a visible status that can be filtered, counted, or referenced elsewhere. With a date in A2, for example, a status formula could return a label based on whether the date is earlier than today. Remember that formulas using TODAY() are dynamic, so a status can change after recalculation just like the formatting rule.
For a stable historical record, neither a live conditional format nor a live TODAY()-based status is a snapshot. If you need to preserve which items were overdue on a particular date, record that status or date as a value through an approved workflow. Do not expect a dynamic rule to retain yesterday’s appearance as a permanent audit trail.
Frequently Asked Questions
What formula highlights dates before today in Excel?
Use =A2<TODAY() in a formula-based conditional-formatting rule, replacing A2 with the first cell in the selected range. It highlights dates earlier than today, not dates equal to today.
How do I highlight overdue dates but ignore blanks?
For a range beginning at A2, use =AND(A2<>"",A2<TODAY()). If the cells may contain text as well as blanks, use =AND(ISNUMBER(A2),A2<TODAY()).
Does Excel update the rule every day?
Yes, the rule evaluates TODAY() using the current date when the workbook recalculates. A workbook set to manual calculation may need recalculation; the rule is not a permanent record of the date on which a cell first became overdue.
How can I highlight dates that are today or earlier?
For date-only values, use a less-than-or-equal comparison with TODAY(). If cells include times and all times today should qualify, use a comparison against TODAY()+1 instead, because TODAY() is midnight at the beginning of today.
Why is a date that looks old not highlighted?
It may be stored as text rather than as an Excel date serial, or the rule may reference the wrong cell or range. Check the value with =ISNUMBER(A2) and review the rule’s formula and Applies to range in Manage Rules.
Related
- Excel Number Format to Show Values in Thousands
- Excel Number Format: Display Zero Values as Dashes
- Excel Number Format With Leading Zeros: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter With Multiple Criteria: A Practical GuideTo filter a worksheet by more than one condition in Excel for Microsoft 365 on Windows, select your…
- Excel MAX IF Formula: Find the Largest Matching ValueTo find the largest value in Excel that meets a condition, use MAXIFS. For example, with categories in…
- Excel Formula for Percent Change CalculationWith the original value in B2 and the new value in C2, enter =(C2-B2)/B2 in D2 to calculate…
- Excel Date Difference in Days: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel Percent Change: Formula, Examples, and TipsTo calculate percent change in Excel, subtract the old value from the new value, then divide by the…
- Excel Let Text Overflow? What Happens to Cell TextYes. In Excel for Microsoft 365 on Windows, text can extend visually past a cell’s right edge when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.