Excel Highlight Dates Older Than Today Automatically

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND($A2<>"",$A2<TODAY()) if your dates begin in A2. Choose a format, such as a fill color, and confirm; dates before today will be highlighted while blank cells are ignored.

Set up the conditional formatting rule

Conditional formatting changes a cell’s appearance when a condition is true. It does not alter the stored date, so you can still sort, filter, calculate with, or replace the value as usual. The key is to apply a formula rule to the cells you want Excel to evaluate.

  1. Select the date range, for example A2:A100. If you want to highlight an entire row based on the date in column A, select the rows or other columns in the data range instead.
  2. On the Home tab, choose Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the formula shown above, then choose Format to set a fill, font color, border, or other available appearance.
  5. Choose OK to close the formatting dialog, then OK again to create the rule.

The formula assumes the selected range starts in row 2 and that the date being checked is in column A. If your range starts in a different row, change the row number in the formula to match the first selected row. The dollar sign before A locks the column, while the row number stays relative so Excel can check each row in turn.

What the formula checks

In the example, TODAY() returns the current date according to the computer’s date settings. The less-than sign asks whether the value in A2 comes before that date. The AND condition also checks that the cell is not empty, avoiding a common surprise: Excel stores dates as numbers, and an empty cell can otherwise behave like a very early numeric value in a comparison.

For example, if today is October 15, 2026, a valid date of October 14, 2026, satisfies the comparison and receives the selected format. October 15 does not, because it is not earlier than today. October 16 does not either. The result updates as the current date changes, so the rule can keep marking overdue dates without you editing the formula each morning.

The formula is entered in the conditional-formatting rule, not in a worksheet cell. It returns TRUE or FALSE for each cell Excel evaluates; it does not display a result in the date column. If you want a separate text status such as “Past due,” that is a different worksheet formula and can be used alongside the formatting.

Highlight an entire row based on its date

For a task list, you may want the whole row to stand out when its due date has passed. Suppose the date is in column C and the data occupies A2:F100. Select A2:F100, open the formula-based rule, and use the following formula.

With due dates in C2:C100, the selected range beginning at A2, and each row’s date in column C:

=AND($C2<>"",$C2<TODAY())

The dollar sign fixes the date column as C, while the relative row reference changes from 2 to 3, 4, and so on as Excel evaluates the selected rows. If you instead used $C$2, every row would be judged using only the date in C2. If you used C2 without a dollar sign, the reference could shift across columns, so parts of a row might be evaluated against the wrong cells.

Make sure the reference row matches the first row of the range you selected. For example, if your data begins in row 5, select the relevant range beginning in row 5 and use row 5 in the formula. A mismatch can make the formatting appear one row above or below the intended date.

Adjust the rule for different date layouts

If your dates are in another column, replace A with that column’s letter. If your date range begins at row 3, change the formula’s row reference to 3. The rule is based on the relationship between the formula and the top-left cell of the selected range, not simply on the first date in the workbook.

  • Dates in column D: select the date cells or the rows to format, then use a D-column reference.
  • Only highlight dates in a short list: select just the date cells, such as A2:A25.
  • Highlight a whole table row: select the table’s body range and lock the date column while leaving the row relative.
  • Use a different color: edit the rule’s Format options; the comparison formula does not dictate the appearance.

To inspect or modify a rule later, select an affected cell and go to Home > Conditional Formatting > Manage Rules. Check the “Show formatting rules for” setting if you cannot see the rule; it may be showing rules for the current selection rather than the entire worksheet. From the manager, you can edit the formula, change the format, adjust the Applies to range, or remove the rule.

Make sure Excel recognizes the values as dates

A cell that looks like a date is not necessarily stored as a date. Excel commonly stores recognized dates as serial numbers and displays them using a date format. But imported data may contain text such as “2026-10-14” that only resembles a date. A text value may not compare as expected with TODAY(), so the conditional format may fail to highlight it even though it looks right.

To check, select a suspect cell and look at the formula bar and number format. Changing the number format to a date is useful only if the value is already a numeric date; formatting alone does not convert text into a date. Depending on the source and regional date conventions, you may be able to convert the column with Data > Text to Columns, or use a suitable conversion step when importing. Be careful with ambiguous dates such as 04/05/2026: they can mean April 5 or May 4 depending on locale.

Blank-looking cells can also contain spaces or formulas that return an empty string. The formula’s nonblank test handles a truly empty cell and an empty-string result. If cells contain spaces, those are not empty strings, so clean the source data or adapt the condition. Errors in the date range can also interfere with comparisons; fix or handle those errors in the source before relying on the visual warning.

Dates with times and the meaning of “older”

Excel dates can include a time fraction. The basic rule compares a date-time value with today at midnight. That means any timestamp from an earlier calendar day is less than today, while a timestamp later today is greater than today’s midnight and will not be highlighted. For a deadline list where “older than today” means a prior calendar date, this distinction usually gives the desired result: today’s items stay unhighlighted.

If you want to compare only the calendar date and ignore the time component, use INT to remove the fractional time for the comparison. With date-times in A2:A100 and a conditional-formatting range starting in A2:

=AND($A2<>"",INT($A2)<TODAY())

For instance, a value representing October 14, 2026 at 4:30 p.m. is treated as October 14 by this rule. This version is mainly useful when the workbook’s date-time values or intended comparison need to be made explicit. It still expects numeric Excel date-time values; text that resembles a timestamp needs conversion first.

Keep the rule useful as the list changes

A rule applied to A2:A100 covers only that range. If you add a new record in row 101, check whether the Applies to range expanded to include it. You can extend the range in Conditional Formatting > Manage Rules. For a growing list, converting the data to an Excel table can make it easier to add records and keep related formatting and formulas together, though it is still sensible to verify the conditional-formatting range after changes.

Also consider what the highlight means to the people using the workbook. A past date may indicate an overdue task, an expired period, or simply a historical record. Conditional formatting marks dates by comparison; it does not know whether a task is complete or whether a date should be excluded for another reason. If completed records should not be highlighted, add a status condition to the rule based on the status column.

For example, if dates are in A and completion status is in B, a rule can require both a past date and a status other than “Complete.” With dates in A2:A100, statuses in B2:B100, and the selected range beginning on row 2:

=AND($A2<>"",$A2<TODAY(),$B2<>"Complete")

This variation is useful for task trackers, but use the exact status text found in your workbook. Extra spaces or inconsistent labels such as “Done” and “Complete” can make the condition behave differently from what you intended.

Common problems and how to fix them

  • No cells change: confirm the date values are real Excel dates, the formula refers to the correct column, and the Applies to range covers the cells you expect.
  • The wrong row is highlighted: align the formula’s row number with the top row of the selected range. For a row-wide rule, keep the date column fixed with a dollar sign.
  • Empty cells are highlighted: use the nonblank check in the formula, and check whether apparently empty cells contain spaces or other values.
  • Today is highlighted: check that the comparison is strictly less than, not less than or equal to. The latter includes today.
  • The color seems stuck: TODAY updates when Excel recalculates. Automatic calculation is normally enabled, but if calculation is set to Manual, use Formulas > Calculate Now or change the calculation setting.

Because TODAY() is a volatile function, Excel refreshes its result during recalculation. The highlight is therefore a live view, not a permanent record of which dates were past on a particular day. If you need an audit trail, record the relevant date or status as a fixed value rather than depending only on a rule that changes with time.

Excel on Mac and Excel for the web

In Excel for Microsoft 365 on Mac, conditional formatting also uses the Home tab and a formula-based new rule; the dialog layout and labels can differ slightly from Windows. Use the same relative-reference logic and confirm that the rule applies to the intended range. Excel for the web also provides conditional formatting, though the available controls and dialog presentation may differ from the desktop app. If a workbook has complicated or overlapping rules, Manage Rules in the desktop app can make the Applies to ranges and rule order easier to inspect.

Windows users can reach Conditional Formatting from the Home ribbon; there is no need for a macro or add-in for this task. On Mac, ribbon access is likewise available, and keyboard shortcuts for opening ribbon commands can vary with settings and keyboard layout, so the ribbon path is the dependable method.

Frequently Asked Questions

How do I highlight dates before today in Excel?
Select the date range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =AND($A2<>"",$A2<TODAY()) for dates in column A starting at row 2, and choose a format.

Does the rule update automatically tomorrow?
Yes, when Excel recalculates, TODAY() returns the current date and the conditional formatting is evaluated again. If the workbook uses Manual calculation, recalculate it or restore automatic calculation.

How do I highlight dates that are today or earlier?
Use a less-than-or-equal comparison instead of a strict less-than comparison. With dates in A2:A100 and the rule applied from row 2, the formula is =AND($A2<>"",$A2<=TODAY()). This includes today but still excludes blank cells.

Can I highlight the whole row when a date is past?
Yes. Select the full data range, then use a formula that locks the date column and leaves the row relative. If dates are in C and the selected range starts on row 2, use =AND($C2<>"",$C2<TODAY()).

Why is a date that looks old not highlighted?
The value may be text rather than a real Excel date, or the rule may point to the wrong column or row. Confirm the underlying value is a date serial, then check the formula and the rule’s Applies to range.

Can I use this to flag overdue tasks but ignore completed ones?
Yes. Add a condition for the status column, such as requiring the status not to equal “Complete.” Make sure the status text and column reference match your sheet.

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.