Excel Formula to Highlight Dates Before Today in a Range

Formulas & functions · Updated November 19, 2026

If your dates are in A2:A100, select that range and create a formula-based conditional-formatting rule using =AND(ISNUMBER(A2),A2<TODAY()). Choose a fill color, and Excel will highlight numeric dates earlier than today while ignoring blank cells and text.

What the formula checks

TODAY() returns the current date according to your computer’s date settings, without a time component. The less-than comparison means a date must be earlier than today to qualify. A date equal to today is not highlighted; nor are future dates.

ISNUMBER(A2) checks that the cell contains a numeric value. Excel stores genuine dates as numbers, so this condition helps prevent blank cells and text that looks like a date from being treated as overdue. In the example, A2 is the first cell in the selected range. Conditional formatting adjusts that reference for each cell it evaluates.

If the range contains only real dates and no blanks, a shorter rule can work. With dates in A2:A100 and A2 as the selected range’s top cell, use =A2<TODAY(). The longer version is safer for a typical list that may have empty cells or imported text.

Set up the conditional-formatting rule

For Excel for Microsoft 365 on Windows, apply the rule this way:

  1. Select the date cells, such as A2:A100. Do not include the heading unless you intend to format it too.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. With A2:A100 selected and A2 as the top cell, enter =AND(ISNUMBER(A2),A2<TODAY()) in the formula box.
  5. Select Format, choose a fill color or another style, and confirm the dialogs.

To check or change the rule later, select a cell in the range and open Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the rule’s formula and the range shown under Applies to. If the rule is not affecting all intended cells, the applied range is a useful first thing to verify.

In Excel for Mac, conditional formatting is also available from the Home tab, though dialog layouts can differ slightly by release. Excel for the web also supports formula-based conditional formatting, but its controls may be arranged differently. The formula logic remains the same.

Choose the right cell reference

The reference in the rule should match the top-left cell of the range you selected. If dates are in D5:D200, select that range and change the formula’s reference to D5. Excel then evaluates D5 for the first cell, D6 for the next, and so on. A mismatch—such as selecting D5:D200 but writing a formula that starts with D4—can make the highlighting appear shifted by one row.

For dates in one column and a full row of information to highlight, select the rows you want formatted and anchor the date column while leaving the row number relative. For example, with dates in column C, records in A2:F100, and A2 as the top-left cell of the selected area, use =AND(ISNUMBER($C2),$C2<TODAY()). The dollar signs keep the date check in column C as the rule moves across each row; the row number changes as Excel evaluates subsequent records.

Use references deliberately: a dollar sign before a column letter locks the column, and one before a row number locks the row. If you lock both in a range rule, Excel may check the same date cell for every cell in the range. That can be correct for a single control date, but it is usually wrong when each row has its own date.

Understand dates, times, and text

Excel can compare dates because genuine dates are stored as serial numbers. The cell’s display format changes how the value looks, not the underlying date being compared. A cell can show 3-Nov-2026, for example, while Excel evaluates its stored number against the number returned by TODAY(). Formatting a cell to look like a date does not convert text into a genuine date.

A date that includes a time is a decimal value: the date is the whole-number portion and the time is the fraction. Because TODAY() represents midnight at the start of the current day, a timestamp from yesterday is earlier than today, while a timestamp from today is not. That behavior matches the usual meaning of “before today.” If the goal is instead to flag anything earlier than the current moment, the comparison needs a current-time function rather than a date-only one.

Imported data may contain date-looking text, which the numeric check intentionally excludes. If a value does not highlight even though it looks old, check whether Excel recognizes it as a date: select the cell and inspect its value or try changing its number format. Changing the format alone will not repair text. Convert the source values to real Excel dates before relying on date comparisons; the appropriate conversion method can depend on the source’s date order and regional settings.

Make the rule update over time

A TODAY-based rule is dynamic. A date that is in the future today can become eligible after the calendar advances, without editing the formula. TODAY is a volatile function, which means Excel recalculates it as part of workbook recalculation. It does not necessarily refresh at the exact instant midnight passes if the workbook remains open and nothing triggers a calculation.

Excel normally uses automatic calculation. If colors seem stale, check Formulas > Calculation Options > Automatic. You can also prompt a recalculation with F9 on Windows. Reopening the workbook or changing a cell can also lead to recalculation. This is generally preferable to entering a fixed date in the rule, since a typed cutoff date will not move forward by itself.

Common problems and fixes

  • Blank cells are highlighted: Use the ISNUMBER check in the recommended rule. A simple comparison may treat an empty cell as a value lower than a date serial.
  • The wrong row is highlighted: Make the formula reference the top-left cell of the selected range. Check whether the range begins on row 2 or row 3, and adjust the reference accordingly.
  • Dates that look old are ignored: The values may be text rather than Excel dates. Convert them to numeric date values, taking care with regional formats such as month/day/year versus day/month/year.
  • Today is highlighted unexpectedly: Check the underlying value for a time component and make sure the formula uses a strict less-than comparison, not less than or equal to.
  • Only part of the list changes color: Open Manage Rules and check Applies to. Expand the range if new records were added outside it, or use a table so formatting can extend with the data.
  • Another color appears instead: The range may have multiple conditional-formatting rules. Review their order and any stop-if-true settings in the rules manager.

Alternatives for different highlighting needs

For a quick rule with a built-in cutoff, select the date range and look under Home > Conditional Formatting > Highlight Cells Rules for the date-related options available in your Excel release. Those options can be convenient for standard relative periods, but a formula rule is more flexible when you need to exclude blanks, reference another column, or highlight entire rows.

If you only need to identify past dates rather than color them, filtering or sorting may be more useful. A filter can narrow a list to dates before a chosen cutoff, while conditional formatting leaves every record visible and marks the matching ones. For a one-time report, a fixed cutoff date may be appropriate; for a workbook meant to stay current, TODAY() avoids having to revise that date manually.

Excel tables are useful for ongoing lists. If you convert a data range to a table, new rows can inherit table formatting and the table can make the working area easier to manage. Still, check the conditional-formatting rule’s applied range after adding data, especially if the new rows are outside the existing table or were pasted in a way that did not extend it.

Frequently Asked Questions

Does the formula highlight today’s date?
No. The less-than operator flags dates strictly earlier than today. A date equal to today is excluded, as is any future date.

Why use ISNUMBER in the formula?
It limits the rule to numeric values, including genuine Excel dates. That helps avoid highlighting blanks and prevents date-looking text from being mistaken for a valid date.

Can I highlight an entire row when its date is past?
Yes. Select the rows or record area, then refer to the date column with a dollar sign before its letter and keep the row number relative. For dates in C2:C100 and selected records beginning at A2, the reference should check column C for each row.

Will the highlighting update tomorrow?
It updates when Excel recalculates the workbook. If the workbook is left open across midnight and the color does not change, trigger a recalculation or check that calculation is set to Automatic.

Can I use this for dates in another column?
Yes. Replace A2 in the formula with the first date cell in the selected range. For dates in F4:F50, select that range and use F4 as the relative reference.

Does changing a cell’s format turn text into a date?
No. Number formatting changes the display of a value; it does not convert text to an Excel date. Convert text values to real dates first, using a method that matches their original date order.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.