To highlight dates before today in Excel, select the date cells and create a formula-based conditional formatting rule using =AND(ISNUMBER(A2),A2<TODAY()), replacing A2 with the first cell in your selected range. Choose a fill or font style, and Excel will mark valid dates earlier than the current date while leaving blanks and text alone.
Set up the rule in Excel for Microsoft 365
In Excel for Microsoft 365 on Windows, first select the cells containing the dates—for example, A2:A100. The formula must refer to the first cell in that selection, not the entire range. Then create the rule:
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=AND(ISNUMBER(A2),A2<TODAY()). - Select Format, choose a fill, font, border, or number format, and confirm.
- Check that the rule applies to the intended range, such as
=$A$2:$A$100, then select Apply or OK.
Use the row number of the top cell in the selected range. If your dates begin in D5, select the date range beginning at D5 and use =AND(ISNUMBER(D5),D5<TODAY()). The formula uses a relative reference so Excel can test each cell in the range individually. On Mac, the same formula works; use Home > Conditional Formatting > New Rule and choose the formula-based rule. Excel for the web also supports conditional formatting rules, though the dialog layout can differ slightly.
What “before today” means
The comparison A2<TODAY() is true when the value in A2 is earlier than the start of today. Excel stores genuine dates as serial numbers, so a chronological comparison works even if the cell is displayed as “Nov 2, 2026,” “2-Nov-26,” or another date format. The display style does not change the underlying value.
TODAY() returns the current date without a time component. The less-than sign excludes today itself: a date equal to today does not qualify, and a future date does not qualify. For example, if the current date is November 3, a valid November 2 date is highlighted, while November 3 and November 4 are not. The rule does not mean “within the last 24 hours”; it means earlier than the current calendar date.
Why the formula checks for a number
Excel treats empty cells as zero in some comparisons. Since date serial numbers for modern dates are positive, a simple rule such as =A2<TODAY() can accidentally format blank cells in the selected range. The added ISNUMBER(A2) test makes the rule true only when the cell contains a numeric value, which is how Excel stores real dates.
This check also prevents text that merely looks like a date from being treated as a valid date by this rule. For example, a pasted value such as “11/02/2026” may be stored as text if it was imported with a format Excel does not recognize. The formula will not highlight it, which is a useful signal that the value needs cleaning or conversion. A date displayed with a date format is not necessarily a true date; its underlying value matters.
Highlight an entire row based on its date
You can use a date in one column to format other cells on the same row. Suppose dates are in column C and the records occupy A2:F100. Select A2:F100, then create a formula rule using =AND(ISNUMBER($C2),$C2<TODAY()). The dollar sign fixes the date column at C while leaving the row number relative, so each row is judged by its own date in column C.
Keep the row number in the formula aligned with the first row of the selected range. If the selection begins at row 2, use row 2 in the formula. Locking the column is important here: without $C, Excel could shift the tested column as it applies the rule across the selected row. You can use the same approach for a task list, invoice register, or project schedule, changing the column letter and selected range to match your layout.
Choose between a formula rule and a built-in rule
Excel’s built-in date rules can be quicker when you want a common preset such as yesterday, today, tomorrow, or a recent period. They are available under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Those options are convenient for ready-made time periods, but a formula is a clearer choice when the exact condition is “any date earlier than today,” with blanks excluded.
A formula rule is also more flexible: it can format full rows, combine conditions, or use a date in another column. For a single straightforward date column, the formula rule is usually easy to inspect and adapt. Avoid creating overlapping rules without a reason. If two rules apply different formats to the same cell, their order and the rule settings can affect what appears; review them under Conditional Formatting > Manage Rules.
Dates that include times
Excel date-times contain a date serial plus a fractional part for the time. Because TODAY() represents midnight at the beginning of the current date, a timestamp from yesterday is less than today and is highlighted. A timestamp from today is later than midnight today and is not highlighted. This behavior normally matches the meaning of “before today” when the intention is to flag earlier calendar dates, not events that happened earlier today.
If you instead need to flag timestamps earlier than the current moment, use NOW() in place of TODAY(), while retaining the numeric check: =AND(ISNUMBER(A2),A2<NOW()). That is a different test, because it can highlight a time from earlier today. Be specific about whether your data represents dates only or dates and times before choosing between the two functions.
When and how the highlighting updates
The rule is dynamic: it compares each value with TODAY() rather than with a date typed into the rule. A date that is in the future today can become eligible after the calendar date advances, and a previously highlighted date remains earlier than today. Conditional formatting updates as Excel recalculates the workbook; it is not a permanent mark applied to the cell.
Excel’s calculation mode can affect when formula results refresh. In the usual Automatic calculation mode, formulas recalculate as the workbook changes and recalculates. If the date highlighting appears stale, check Formulas > Calculation Options and confirm that calculation is not set to Manual. With Manual calculation, the displayed result may not update until recalculation occurs. Because TODAY() is a volatile function, workbooks with many formulas using it can also recalculate more often than workbooks that use fixed date values.
Common problems and how to fix them
- Nothing is highlighted: Check that the cells contain real Excel dates, that the rule’s formula points to the first cell in the applied range, and that the rule’s “Applies to” range includes the dates you expect.
- Blank cells are highlighted: Use the version with
ISNUMBER, rather than only comparing the cell withTODAY(). - The wrong rows are highlighted: Check whether the formula’s row number matches the first selected row. For whole-row formatting, lock the date column, as in
$C2, but leave the row relative. - Imported dates do not respond: They may be stored as text. Convert them to actual dates before expecting a numeric date comparison to work. A cell’s visual date format alone does not convert text into a date value.
- Today’s entries are unexpectedly highlighted: Confirm that the formula uses a strict less-than sign, not a less-than-or-equal-to comparison. Also check whether the stored value contains a time and whether your intended cutoff is the start of today or the current moment.
When diagnosing a rule, inspect it through Home > Conditional Formatting > Manage Rules. Confirm both the formula and the applied range. A correct formula attached to the wrong range can look like a calculation error, especially when the data begins below row 1 or the rule was copied from another sheet.
Other useful variations
To flag only dates in the past while leaving dates before a chosen historical cutoff out, compare the date column against a date stored in a cell instead of using TODAY(). To distinguish overdue records from completed ones, add a status condition. For example, if dates are in A2:A100 and statuses are in B2:B100, apply the rule to the relevant rows and use =AND(ISNUMBER($A2),$A2<TODAY(),$B2<>"Complete"). This flags past dates only when the status is not Complete. Match the text in the formula to the status values in your sheet.
Conditional formatting changes appearance, not the stored date, and it does not sort records or send reminders. If you need a printable list of overdue items, a filter or a separate formula-based status column may be more useful alongside the color rule. Formatting is best treated as a visual cue: keep the underlying dates and status values accurate so the rule continues to reflect the actual records.
Frequently Asked Questions
How do I highlight dates older than today in Excel?
Select the date range and create a formula-based conditional formatting rule with =AND(ISNUMBER(A2),A2<TODAY()), changing A2 to the range’s first cell. Pick a format and confirm the rule applies to the selected cells.
Does Excel’s “before today” rule include today?
No. A strict less-than comparison excludes a value equal to today. Use a less-than-or-equal comparison only if you specifically want to include today as well.
Why are empty cells highlighted as dates in the past?
A basic comparison can treat a blank like zero, which is below a modern date serial number. Add ISNUMBER to the formula so only numeric date values can qualify.
Can I highlight a whole row when its date is overdue?
Yes. Select the full table range and write the formula using an absolute date-column reference and a relative row reference, such as =AND(ISNUMBER($C2),$C2<TODAY()) when dates are in column C and the first selected row is 2.
Will the rule update automatically tomorrow?
It updates when Excel recalculates the workbook. Automatic calculation is the normal setting; if the workbook is set to Manual, the displayed formatting may remain unchanged until recalculation.
Related
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
- Comparison Plot in Excel With R²: Steps and Meaning
- Company Invoice Template in Excel: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Inventory Spreadsheet: Simple Setup and Useful TipsYes—an inventory spreadsheet can be simple: list each item once, record its quantity and reorder point, and update…
- Add Page Numbers in Microsoft Word 365To add page numbers in Word 365 on Windows, open the document, select Insert > Page Number, choose…
- How to Archive Emails in Outlook 365To archive email in Outlook 365, select a message and choose Archive on the toolbar or ribbon. Outlook…
- How to Set Out of Office in Outlook on a Windows PCTo set an out-of-office reply in Outlook on a Windows PC, open Automatic Replies, switch them on, write…
- GPT for Excel Data Analysis: Uses, Limits, and TipsYes. GPT tools can analyze Excel data: upload a workbook to ChatGPT with data-analysis features enabled, or use…
- How to Switch Back to the Old Outlook View on WindowsTo switch back to the old Outlook on Windows, turn off the New Outlook toggle near the upper-right…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.