To highlight dates in Excel that fall before a cutoff, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, then enter the cutoff date. For a rule that updates automatically or refers to a date in another cell, use a formula-based conditional formatting rule instead.
What “dates before” means in Excel
A date before a cutoff is any date earlier than the date you specify. For example, if a cell contains April 9 and your cutoff is April 10 of the same year, April 9 is before the cutoff and qualifies for highlighting. A date equal to the cutoff does not qualify: “before” means less than, not less than or equal to.
Excel stores valid dates as serial numbers, so a conditional formatting rule can compare dates much like it compares other numbers. The date’s display format—such as 9-Apr, 4/9/2026, or April 9, 2026—does not change the comparison. What matters is whether Excel recognizes the cell value as a date, rather than as text that merely looks like one.
This is useful for overdue tasks, expired records, past appointments, and deadlines. It can also help distinguish past dates from upcoming ones without sorting the list or changing its contents.
Use the built-in Less Than rule
For a fixed cutoff, the built-in rule is often the quickest option in Excel for Microsoft 365 on Windows:
- Select the cells containing dates, such as A2:A100. Avoid including a heading unless you want Excel to evaluate it too.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than.
- Enter the cutoff date in the dialog. You can type a date Excel recognizes, such as 12/31/2026, and choose a formatting style.
- Select OK, then check a date on either side of the cutoff to confirm the rule behaves as intended.
The rule highlights dates strictly earlier than the value entered. It is a good choice when the cutoff is a one-time value and you do not expect to change it often. For a cutoff that should be controlled from a worksheet cell, or a comparison that needs additional conditions, a formula rule is more flexible.
On Excel for Mac, the Conditional Formatting commands are also on the Home tab, though the dialog appearance can differ by release. Excel for the web offers conditional formatting from Home > Conditional Formatting; the available dialog controls may differ from desktop Excel. If a particular rule option is not shown in your version, use the formula-rule method if that option is available.
Compare dates with a cutoff cell
A cutoff stored in a worksheet cell is easier to update than a date typed into a rule. For example, put the cutoff date in E1 and apply a formula rule to A2:A100. With dates in A2:A100 and the cutoff in E1, use this formula:
=AND(ISNUMBER(A2),A2<$E$1)
To create the rule, select A2:A100, then 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, choose Format to select the appearance, and confirm. In the formula, A2 is a relative reference so Excel can check each row in the selected range; $E$1 is absolute so every row uses the same cutoff cell.
ISNUMBER makes the rule ignore blanks and text entries instead of treating them as dates. The cutoff cell should itself contain a valid Excel date. Format E1 as a date if you want the value to be easy to read, but remember that formatting changes how it looks, not the underlying value.
Highlight dates before today
To flag dates that have already passed as of the current day, use TODAY rather than entering a fixed cutoff. With dates in A2:A100, select that range and create a formula-based rule using:
=AND(ISNUMBER(A2),A2<TODAY())
TODAY returns the current date, without a time component. Excel recalculates the rule as the workbook recalculates, so a date can become highlighted after the calendar moves past it. This makes the rule useful for a live overdue list, but it also means the displayed result can change over time even when nobody edits the date column.
Because the comparison is strictly less than, a date equal to today is not highlighted. To include today as well, change the comparison to less than or equal to. With dates in A2:A100, the corresponding formula is:
=AND(ISNUMBER(A2),A2<=TODAY())
Use this inclusive version only if “today or earlier” is what you mean. If your rule is for deadlines that are already overdue, excluding today may be more appropriate.
Highlight an entire row for an overdue date
Sometimes the useful result is highlighting a complete record, not just its date. Suppose each record occupies columns A through D, the due date is in column A, and the cutoff is in E1. Select A2:D100 and create a formula rule with:
=AND(ISNUMBER($A2),$A2<$E$1)
The dollar sign before A locks the date column as Excel applies the rule across the selected row. The row number remains relative, so each record is checked against its own date. The cutoff reference is fixed in both directions. If the formula references are arranged incorrectly, Excel may test another column or row and highlight records unexpectedly.
You can add another condition when some past-due records should not be highlighted. For example, if dates are in A2:A100 and statuses are in B2:B100, and completed items have the status Complete, use:
=AND(ISNUMBER($A2),$A2<$E$1,$B2<>"Complete")
Apply that rule to A2:D100 to highlight the whole row only when its date is before the cutoff and its status is not Complete. Make sure the text in the formula matches the status spelling in the worksheet.
Before a date is different from before the current time
When cells contain dates with times, a date-only comparison has a useful but sometimes surprising result. Excel stores the time as a fractional part of the date serial number. A value for April 10 at 3 p.m. is later than April 10 at midnight, but both values display as April 10 if the cells use a date-only format.
As a result, comparing a timestamp with TODAY treats all times on today’s date as later than today’s midnight. They will not be flagged by a strict “before today” rule. If you need to highlight timestamps earlier than the current moment, compare them with NOW instead. With timestamps in A2:A100, use:
=AND(ISNUMBER(A2),A2<NOW())
NOW returns the current date and time, and its displayed value may update when Excel recalculates. Choose TODAY for calendar-day logic, such as dates before today; choose NOW for elapsed-time logic, such as appointments whose start time has passed. If you need to flag all timestamps from earlier calendar days but not any time today, TODAY is generally the relevant cutoff.
Check the range, references, and rule order
If the formatting appears on the wrong cells or does not appear at all, check the rule setup before changing the data. Select a cell in the intended range and open Home > Conditional Formatting > Manage Rules. Inspect the rule’s formula, formatting, and Applies to range. A correct formula applied to the wrong range can look like a formula error.
- Check the first-cell reference. If the Applies to range starts at A2, a formula that checks each row normally begins with A2, not A1 or A3.
- Check absolute references. Lock a cutoff cell such as E1 as $E$1. When highlighting rows based on a date in column A, lock the column as $A2 but leave the row relative.
- Check for text dates. If a date is stored as text, comparisons may not work as expected. Re-enter or convert the value to a real date, then test the rule again.
- Check overlapping rules. Multiple conditional formatting rules can apply to the same cells. Manage Rules lets you review their order and adjust them when one format seems to mask another.
- Check the cutoff value. A blank or text cutoff cell is not a reliable date comparison. Enter a valid date in the cutoff cell.
For a quick check, change the cutoff temporarily to a date that should clearly include some entries and exclude others. Restore the intended date after confirming the rule. This helps separate a rule-setup problem from a data problem.
Choose a rule that matches the job
Use Less Than when you need a simple comparison against a fixed date. Use a formula rule when the cutoff should come from a cell, when blanks must be excluded explicitly, when the rule should update with TODAY or NOW, or when status and date must both be checked. For a large workbook, a clear cutoff cell and a well-defined Applies to range also make the setup easier for someone else to maintain.
Conditional formatting changes the visual appearance of cells; it does not delete, sort, or change the underlying dates. That makes it safer for scanning a working list, but colors alone should not be treated as permanent records of which dates were overdue on a particular day. A TODAY-based rule recalculates, so yesterday’s past-due status may look different after the cutoff moves. If you need a historical snapshot, record that status separately rather than relying only on a dynamic color.
Frequently Asked Questions
How do I highlight dates before today in Excel?
Select the date range, create a formula-based conditional formatting rule, and use the TODAY comparison shown above. The formula excludes today itself; use a less-than-or-equal comparison if today should also be highlighted.
Can Excel highlight dates before a date in another cell?
Yes. Put the cutoff in a cell such as E1 and refer to it in a formula rule. Lock the reference as $E$1 so every date in the selected range is compared with the same cutoff.
Why are some dates not highlighted?
They may be stored as text, fall outside the rule’s Applies to range, or be equal to the cutoff rather than earlier than it. Also confirm that the formula’s starting reference matches the top-left cell of the selected range.
Does “before today” include today?
No. “Before” means strictly earlier, so today is excluded. Use a less-than-or-equal comparison when you want today and earlier dates included.
Will the rule update automatically?
A rule based on TODAY or NOW can update when Excel recalculates. A rule based on a fixed date or cutoff cell changes only when that value or the rule changes.
Related
- Budget vs Actual Excel Template: Free Options and Setup
- Budget Template in Microsoft Excel: Find and Use One
- Bps Number Format in Excel: Display Basis Points
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Creation: How to Set Up and Use TablesTo create an Excel table in Microsoft 365 for Windows, select a cell in a contiguous data range,…
- Duplicate Conditional Formatting in Excel: Find ValuesTo highlight duplicate values in Excel for Microsoft 365 on Windows, select the cells to check, then choose…
- Excel Conditional Formatting for Upcoming DatesYes. In Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting >…
- How to Auto Forward Emails in OutlookTo auto forward every new message in Outlook, open Settings, go to Mail > Forwarding, turn on forwarding,…
- Excel Consolidate Tool: How to Combine DataExcel’s Consolidate tool combines values from multiple ranges into one summary: in Excel for Microsoft 365 on Windows,…
- How to Lock Cells in Excel and Protect a WorksheetTo lock cells in Excel, select the cells, open Format Cells, check Locked, then go to Review >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.