To highlight dates before today in Excel for Microsoft 365 on Windows, select the date cells, then 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 select OK. The rule updates as the current date changes, while leaving blank cells and text unformatted.
Set up a conditional formatting rule
Conditional formatting changes how a cell looks when a condition is true; it does not change the cell’s value. A formula-based rule is useful here because “before today” is a moving condition. Unlike a one-time fill color, it can reassess the dates as Excel recalculates.
- Select the date cells, such as A2:A100. Exclude the heading row if it contains a label rather than a date.
- On the Home tab, select Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=AND(ISNUMBER(A2),A2<TODAY()). Use the first cell in the selected range, not the entire range, in this formula. - Select Format, choose a fill, font, border, or other appearance, and select OK. Select OK again to create the rule.
The reference A2 is relative, so Excel checks A2 in the first row, A3 in the next, and so on. If your dates start in a different cell, replace A2 with the top-left cell of your selection. For instance, if the selected range is C5:C80, use C5 in the formula. A mismatched starting reference can make the formatting appear shifted by a row or column.
In Excel for Mac, the same formula-based conditional formatting approach is available, although dialog layouts can vary slightly by release. In Excel for the web, conditional formatting is available too; if a rule is difficult to create or adjust in the browser, open the workbook in the desktop app and use the steps above.
What the formula checks
Excel stores real dates as serial numbers, so it can compare one date with another. TODAY() returns the current date, and the less-than operator checks whether the cell’s date is earlier. AND requires both tests to be true: ISNUMBER confirms the cell contains a numeric date value, and the comparison confirms it is in the past.
That ISNUMBER check is important in ordinary worksheets. A blank cell can otherwise behave unexpectedly in comparisons, and a value that only looks like a date may actually be text. With the guard in place, only numeric date values earlier than today receive the format. The formula does not require a helper column, and it does not overwrite the original date.
TODAY() is a function that recalculates rather than a date permanently recorded when the rule is created. As the calendar advances, a date that was today becomes a past date and can be highlighted the next time Excel recalculates. The exact timing depends on calculation settings and when the workbook is opened or recalculated; it is not a live clock that refreshes every second.
Choose the right meaning of “before today”
The basic rule treats a date earlier than today as past and excludes today. That is generally right for due dates, birthdays, and date-only records. However, a cell can contain a date and a time even when its display format shows only the date. Excel stores the time as a decimal part of the date serial, which matters if you want to include or exclude events occurring today.
- Past dates only: Keep the less-than-TODAY comparison in the main rule. A date entered for today is not highlighted.
- Past dates and anything occurring today: If timestamps may be present and all of today should count, use a next-day boundary. For dates or timestamps in A2:A100, use
=AND(ISNUMBER(A2),A2<TODAY()+1). This includes times later today but also includes all earlier dates. - Earlier than the current moment: If the cells contain timestamps and you mean strictly earlier than right now, use
=AND(ISNUMBER(A2),A2<NOW())for A2:A100. NOW() includes a time component, so a timestamp earlier today can qualify.
Choose based on what the data represents. A task due today is usually not overdue until the day ends, whereas an appointment at 9 a.m. may be in the past at 10 a.m. If you need a precise deadline, store the time as well as the date and use a time-aware rule.
Highlight an entire row from its date
Sometimes the date is in one column, but you want to shade the whole record. Suppose each task occupies columns A through F, the date is in column D, and the first data row is row 2. Select A2:F100, then create a formula rule using =AND(ISNUMBER($D2),$D2<TODAY()). Set the format and apply the rule.
The dollar sign before D locks the date column as Excel evaluates cells across a row; the row number remains relative so each record checks its own date. If the date is in column B instead, lock B. Make sure the “Applies to” range starts on the same row used in the formula. This technique can make an overdue task stand out without adding a separate status column.
Fix dates that do not highlight
If a rule appears correct but nothing changes, first check whether the cells contain actual Excel dates rather than text. A date imported from a website or a CSV file can look like 4/12/2026 but be stored as text. Changing the number format alone does not convert text into a date, so the comparison may not work as expected.
- Check the cell value: Select a date cell and inspect the formula bar. Try changing its number format to General; a true date typically displays as a serial number. Restore the preferred date format afterward.
- Convert text dates: Depending on the imported data, Text to Columns or a suitable date conversion may help. Review the results carefully, especially when day and month order could be ambiguous.
- Check the rule range: Go to Home > Conditional Formatting > Manage Rules and confirm the rule applies to the intended cells. The range and the formula’s first-cell reference must align.
- Review rule conflicts: Another conditional formatting rule may also apply to the range. In Manage Rules, inspect rule order and any Stop If True setting, which can affect whether later rules are evaluated.
- Check calculation mode: If formulas are not updating, verify that calculation is set to Automatic under Formulas > Calculation Options. You can also recalculate with F9 on Windows; on Mac, the equivalent shortcut can depend on keyboard settings.
Also check for hidden spaces or apostrophes in imported values, and confirm that the workbook’s regional date interpretation is appropriate. For example, 03/04/2026 can mean March 4 or April 3 depending on the format and locale. A display that looks plausible is not proof that Excel interpreted the value as the intended date.
Formula rule or built-in date rule?
Excel’s Conditional Formatting menu includes preset date rules under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Those presets are convenient for named periods such as yesterday, today, or a recent interval when the available option matches the need. They are not as flexible as a formula when the requirement is specifically “any date before today.”
A formula rule is also preferable when you need to ignore blanks, check timestamps, use a date in another column, or format a complete row. The trade-off is that the formula must be written relative to the selected range. For a simple one-off visual distinction, a preset may be quicker; for a rule that must precisely express your data logic, the formula offers more control.
Maintain the rule as your data grows
If you add more rows below A100, the original rule may not automatically cover them unless the range expands. You can extend the range in Home > Conditional Formatting > Manage Rules by editing its “Applies to” field. Alternatively, convert the data range to an Excel table with Insert > Table. Tables expand as rows are added, but it is still sensible to confirm that the conditional formatting rule has extended to new rows.
Keep the format meaningful and consistent. A subtle red fill or a contrasting font can identify past dates without making the sheet difficult to read. If the workbook uses several rules, document what each color means or pair color with a text status. Color alone can be hard to interpret for some readers and may not print clearly.
Frequently Asked Questions
Will Excel automatically highlight dates that become past tomorrow?
Yes, provided the rule remains in place and Excel recalculates. TODAY() changes with the current date, so a date that is not past today can satisfy the rule on a later day. Recalculation settings and workbook activity affect when the display refreshes.
How do I highlight dates before today but ignore blank cells?
Use the ISNUMBER guard in the conditional formatting formula: =AND(ISNUMBER(A2),A2<TODAY()), assuming the selected date range starts at A2. This checks that the cell contains a numeric date before comparing it with today.
Does this rule include today?
No. The less-than comparison excludes a date equal to today. For timestamp data, it also excludes timestamps later today because they are greater than today’s midnight. If all of today should count, use the next-day boundary described above.
Can I highlight dates before today in another column?
Yes. To color a whole row based on a date in column D, select the row range beginning at row 2 and use =AND(ISNUMBER($D2),$D2<TODAY()). Lock the date column with a dollar sign and keep the row relative.
Why does the rule not work for dates imported from a file?
The imported values may be text rather than Excel date serials, even if they look like dates. Convert them to genuine dates, check ambiguous day/month order, and then verify the rule’s range and top-left-cell reference.
Related
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
- Excel Data Validation List Without a Cell Reference?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find Track Changes in Excel (and View Edits)To find recent edits in Microsoft 365 Excel for Windows, open the workbook and select Review > Show…
- Google Sheets Wrap Text Shortcut and Menu OptionsIn Google Sheets, use Format > Wrapping > Wrap to wrap text in selected cells. There is no…
- Box and Whisker Plot in Excel: Set It Up Step by StepTo create a box and whisker plot in Excel for Microsoft 365 on Windows, arrange each group of…
- Excel Conditional Formatting Arrows for Showing ChangeExcel’s conditional-formatting arrows can show whether values are relatively high or low, but they do not automatically calculate…
- Share a Specific Calendar in Outlook: Access and StepsTo share one calendar in Outlook, select that calendar—not just the Calendar view—open its sharing settings, add the…
- How to Remove a Horizontal Page Break in Excel QuicklyTo remove a manual horizontal page break in Excel for Microsoft 365 on Windows, select the row immediately…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.