To highlight dates earlier than today in Excel for Microsoft 365 on Windows, select the date cells (for example, A2:A100), create a formula-based conditional-formatting rule, and use =AND(A2<>"",A2<TODAY()). Choose a format such as a red fill. This rule ignores blanks and highlights dates before the current date, not a date equal to today.
Set up the rule in Excel
Here is the full procedure for a date list in A2:A100. The formula’s A2 reference should match the top-left cell of the selected range; Excel adjusts it for each row.
- Select A2:A100, or select the actual range containing your dates.
- On the ribbon, choose Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
- Enter the formula shown above in the formula box. Do not type an initial apostrophe before the equals sign.
- Select Format, choose a fill, font, or border, and select OK.
- Confirm the rule and its Applies to range are correct, then select OK again.
The color is only a visual cue: it does not change the cell’s value or sort the records. To verify the rule, enter a genuine past date in the selected range and check that it receives the format. A date for today should not.
What the formula checks
Excel stores real dates as serial numbers, so comparing a date with TODAY() is a numeric comparison. TODAY() returns the current date, without a time component. The less-than sign means strictly earlier: yesterday and older dates match, while today and future dates do not.
The formula uses AND to make the blank check explicit. With dates in A2:A100 and the rule applied beginning at A2, the condition A2<>"" tests whether the cell is not empty, and A2<TODAY() tests whether its value is earlier than today. Both conditions must be true. This prevents an empty cell from being treated like a very early date by the comparison.
If every cell in the range is guaranteed to contain a valid date, the shorter formula also works. For a range beginning at A2, use =A2<TODAY(). The blank-safe version is usually a better default for lists that may contain incomplete rows.
Highlight an entire row based on its date
You can color a complete record row according to the date in one column. For example, assume dates are in column C and records occupy A2:F100. Select A2:F100, create a formula rule, and use =AND($C2<>"",$C2<TODAY()). Set the format, and make sure the rule applies to the full A2:F100 range.
The dollar sign fixes the column reference at C, so Excel checks the date column even as the rule formats cells in A, B, D, or another column. The row number remains relative, allowing row 3 to check C3, row 4 to check C4, and so on. If the date is in another column, change C to that column. If the selected range starts on a different row, use that first row number in the formula.
A common reference mistake is locking both the column and row, as in $C$2. That would make every row check only C2. For a rule applied across multiple rows, the column is typically fixed and the row left relative.
Choose the right date range
“Before today” can mean different things in a workflow. The basic rule marks every earlier date, whether it is one day old or several years old. If you only want to flag items that are overdue, that may be exactly right. If you want a recent-period warning, a separate threshold is needed.
For example, with dates in A2:A100, a rule that highlights dates in the last seven days but still before today can use =AND(A2<>"",A2<TODAY(),A2>=TODAY()-7). The boundary is inclusive: it includes the date seven days before today but excludes today. Adjust the number of days to suit the process, and make sure the rule’s formula still begins with the top-left cell of its applied range.
Another useful distinction is “overdue” versus “completed.” If an old date should remain visible but no longer count as overdue after a task is completed, add a status condition or use a separate status-based rule. For instance, a list could use column A for due dates and column B for status. For data beginning in row 2, a formula can require both an earlier date and a status that is not Complete: =AND($A2<>"",$A2<TODAY(),$B2<>"Complete"). Choose the status spelling and capitalization to match the values in your sheet.
How TODAY updates
TODAY() is a dynamic function. The rule does not permanently record the date on which it was created; it compares each cell with the current date when Excel recalculates. Consequently, a date that is today will normally become “before today” after the calendar day changes, provided the workbook recalculates.
Excel’s calculation setting is usually automatic. If the highlight appears out of date, check Formulas > Calculation Options and select Automatic if appropriate. You can also recalculate with F9 on Windows. On a Mac, the common recalculation shortcut is Fn+F9, although keyboard settings and hardware can affect the key combination. Recalculation updates formulas; it does not change the dates typed into the cells.
Because TODAY changes with the computer’s current date, it is useful for live lists such as deadlines and aging reports. It is not suitable when you need a fixed historical cutoff that must never move. For a fixed cutoff, enter the chosen date in a cell and compare against that cell instead, or replace the dynamic date with a fixed value. Document the cutoff so another person can tell whether the report is meant to update.
Dates with times and dates stored as text
A real Excel date-time includes a fractional part for the time of day. Since TODAY() represents midnight at the start of today, a timestamp from earlier today is still greater than today’s midnight and will not be highlighted by the basic before-today rule. A timestamp from yesterday is earlier and will be highlighted. This behavior is usually right when the question is whether something happened before the current calendar day.
If instead the rule should flag any timestamp earlier than the current moment, compare against NOW() rather than TODAY(). With timestamps in A2:A100, a blank-safe rule is =AND(A2<>"",A2<NOW()). NOW() returns the current date and time, but like TODAY it updates when Excel recalculates, not as a continuously running clock.
Text that looks like a date is a different problem. A value pasted from another system may appear as 10/12/2025 but be stored as text, especially if its format does not match the workbook’s regional settings. The comparison may then give unexpected results or fail to behave like a date comparison. Check whether the cell is a real date by changing its number format temporarily to General: a real date will display as a serial number. Convert text dates to genuine dates before relying on the rule, using a suitable conversion method such as Text to Columns or a formula appropriate to the source format.
Common problems and fixes
- Nothing is highlighted: Confirm the formula refers to the first cell in the Applies to range, and check that the entries are actual dates rather than text. Also confirm that the dates are genuinely earlier than today.
- Every row has the same result: Look for an accidentally fixed row reference such as
$A$2. For a column-based rule across rows, use a relative row reference such as$A2. - Blank rows are colored: Use the blank-safe AND formula and check that the range does not include unrelated values or formulas returning unexpected content.
- The wrong cells are colored: Open Home > Conditional Formatting > Manage Rules, select the relevant rule, and inspect Applies to. Remove or correct overlapping rules if their formats conflict.
- Dates look right but do not compare correctly: Check for text values, hidden time portions, or imported values that include spaces. Convert the source data to real dates and apply a consistent date format.
Cell formatting and date values are separate. Changing a cell’s display format to a date does not always convert text into a date, and a date may display differently under different regional settings without changing its underlying value. Fix the data itself when comparison results are wrong.
Formula rule versus built-in date rules
Excel’s conditional-formatting menus include built-in date-related rules for common relative periods. They can be quicker when the choice you want is available, such as highlighting a date from yesterday. However, a built-in “yesterday” rule is not the same as “any date before today”: it targets a particular period, while the formula can mark all past dates and can exclude blanks or add other conditions.
Use a formula rule when you need a precise boundary, whole-row formatting, a blank check, or a status condition. Use a built-in rule when its named time period matches the task and you want a quick setup. Both approaches change only the appearance; neither deletes old dates, moves records, or creates a permanent overdue flag.
On Excel for Mac, the same formula logic applies, although dialog labels and ribbon arrangement can differ slightly by release. In Excel for the web, existing conditional formats are generally visible, but creating or managing some formula-based rules may be more limited or presented differently than in the Windows desktop app. If you cannot find the formula-rule option or need to inspect a complex rule, open the workbook in desktop Excel.
Frequently Asked Questions
Does Excel have a built-in rule for dates before today?
Excel offers built-in relative-date choices for common periods, but a formula rule is the flexible option for every date earlier than today. Use =AND(A2<>"",A2<TODAY()) when the applied range begins at A2 and blanks should remain unformatted.
How do I highlight overdue dates but not today?
Use a strict less-than comparison with TODAY, not less-than-or-equal. For dates in A2:A100, the condition A2<TODAY() excludes today while matching earlier dates. Include the blank check if the range may contain empty cells.
Will the highlight update tomorrow?
Yes, when Excel recalculates the workbook. TODAY returns the current date, so dates can move from today to the past as the day changes. If the workbook uses manual calculation, recalculate it or change the calculation setting to Automatic.
Can I highlight the whole row when its due date has passed?
Yes. Select the full record range and use a formula with the date column fixed and the row relative, such as =AND($C2<>"",$C2<TODAY()) for a range beginning on row 2 with due dates in column C.
Why is a date from today not highlighted?
That is expected: “prior to today” means strictly earlier than today. If you need to include today, use a less-than-or-equal comparison, while considering whether timestamps later in the day should also qualify.
Related
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace Shortcut: Ctrl+H and Other TipsIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace, then enter the text…
- Freeze a Group of Rows in Excel: Step-by-Step GuideTo keep several rows visible while you scroll in Excel for Microsoft 365 on Windows, select the first…
- Excel 365 Password Protection: How to Do It SafelyTo password-protect an entire Excel 365 workbook on Windows, open it and choose File > Info > Protect…
- Creating Rules in Microsoft Outlook: A Practical GuideTo create a rule in Outlook for Microsoft 365 on Windows, select a message and choose Home >…
- Excel Export to CSV: Does It Keep Cell Formatting?No. Exporting an Excel worksheet as a CSV file does not preserve cell formatting such as fonts, colors,…
- Excel Find and Replace Wildcards: A Practical GuideYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, enter a pattern in Find what, and use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.