Excel Conditional Formatting for Past Dates

How-to basics · Updated November 22, 2026

To highlight dates that are past (earlier than today) in Excel, select the date range, go to Home > Conditional Formatting > New Rule, choose Use a formula, enter =A2<TODAY() (adjusting A2 to the first cell in your selection), set a format, and click OK. This rule instantly flags any date before the current day, and because TODAY() is volatile, it updates automatically each time the workbook recalculates.

Why Conditional Formatting for Dates Matters

Spreadsheets that track deadlines, invoices, subscriptions, or project milestones all share a common need: seeing at a glance which items are overdue. Manually scanning a column of dates is error-prone, especially as the list grows. Conditional formatting shifts that job to Excel. Once a rule is in place, past dates are highlighted in the format you choose—red fill, bold text, a border—so the eye is drawn immediately to what needs attention. The feature is available in all modern desktop versions of Excel, including Microsoft 365, Excel 2021, 2019, and earlier, with slight differences in menu wording on a Mac.

How the Date Rule Works

Excel’s conditional formatting evaluates a formula against each cell in the selected range. The formula =A2<TODAY() compares the date in A2 to the current date returned by the TODAY() function. If the date is earlier, the condition is TRUE and the format is applied. The key is that the formula must be written relative to the first cell in your selection. For example, if your dates are in cells B2:B50, select that range, start the formula with =B2<TODAY()—Excel adjusts the reference for each row automatically. The TODAY() function is volatile, meaning it recalculates whenever the worksheet changes, so the highlighting stays current even if you open the file days later.

Step-by-Step: Highlight Past Dates

Here is the exact process in Excel for Microsoft 365 on Windows:

  1. Select the range containing your dates (e.g., A2:A100).
  2. Go to the Home tab, click Conditional Formatting, then choose New Rule.
  3. In the dialog box, select Use a formula to determine which cells to format.
  4. In the formula box, type =A2<TODAY() (adjust A2 to match the first cell of your selection).
  5. Click the Format button, choose a fill color, font color, or border on the appropriate tabs, then click OK.
  6. Click OK again to apply the rule.

On a Mac, the steps are identical, but the dialog boxes appear slightly different in styling. In Excel for the web, conditional formatting is available under the Home tab as well, but the formula-based rule option is found under Conditional Formatting > New Rule—the same logic applies.

Choosing the Right Comparison

“Past” is not the only condition you might want. If you need to highlight dates that are past and include today, use <=TODAY(). If you want to exclude weekends or holidays, you’ll need a more complex formula with WORKDAY() or NETWORKDAYS(). For example, to highlight dates that are more than 30 days past, use =A2<TODAY()-30. You can also combine conditions: =AND(A2<>"", A2<TODAY()) ensures blank cells are not highlighted. The table below summarizes common variations:

  • Past only: =A2<TODAY()
  • Past or today: =A2<=TODAY()
  • Past more than 7 days: =A2<TODAY()-7
  • Past more than 30 days: =A2<TODAY()-30
  • Past but not blank: =AND(A2<>"", A2<TODAY())

Common Problems and Fixes

One frequent issue is that the rule highlights the wrong cells because the formula reference is not relative. If you select A2:A10 but type =A$2<TODAY(), the dollar sign locks the row, and every cell compares to A2—causing incorrect results. Always use relative references (no dollar signs) unless you intentionally want a fixed cell. Another problem is that dates stored as text are not recognized as dates by Excel. If your “dates” are left-aligned in the cell, they are likely text. Convert them using DATEVALUE() or the Text to Columns wizard. Also, if the rule seems to not update, press F9 to force a recalculation. Finally, if you have multiple rules on the same range, the order matters—Excel applies the first TRUE rule unless “Stop If True” is checked. Manage rules under Conditional Formatting > Manage Rules.

Comparing to Built-in Date Rules

Excel also offers pre-built date rules under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. That dialog lets you pick “yesterday,” “today,” “last 7 days,” and so on. These are convenient, but they are less flexible than a formula. For instance, the built-in rule cannot highlight dates that are exactly 30 days past, nor can it combine with other conditions like a status column. The formula approach gives you total control. If you only need a quick highlight of yesterday or last week, the built-in rule is faster, but for anything custom, use a formula.

Frequently Asked Questions

Why is my conditional formatting not highlighting past dates?
Most often, the formula reference is incorrect (e.g., using an absolute reference like $A$2 instead of relative A2), or the cells contain text rather than real dates. Check that the date cells are formatted as dates (right-aligned) and that the formula matches the first cell of your selected range.

How do I highlight dates that are past but also blank cells?
To avoid highlighting blanks, add a condition: =AND(A2<>"", A2<TODAY()). The A2<>"" part checks that the cell is not empty.

Can I use conditional formatting to highlight past dates based on another cell?
Yes. For example, to highlight dates in column A that are past relative to a deadline in column B, use =A2<B2. The formula can reference any cell in the same row.

Does the rule update automatically when I open the file tomorrow?
Yes, because TODAY() recalculates each time the workbook is opened or changed. The highlighting will reflect the new current date automatically.

How do I remove conditional formatting from past dates?
Select the range, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Or manage rules and delete the specific rule.

What is the difference between TODAY() and NOW() in this context?
TODAY() returns only the current date (with time set to midnight), while NOW() returns the current date and time. For date-only comparisons, use TODAY() to avoid time-based mismatches.

Alternatives to Conditional Formatting

If you prefer not to use conditional formatting, you can filter dates manually, but that requires constant re-filtering. Another alternative is to create a helper column with a formula like =IF(A2<TODAY(),"Past","") and then filter on that column. However, conditional formatting is the most direct and visual method. For dashboards, you might also use a PivotTable with a calculated field, but that is overkill for simple highlighting. Conditional formatting remains the standard tool for this task.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.