How to Highlight Past Dates in Excel Automatically

To highlight dates before today in Excel, select your date cells and create a formula-based conditional formatting rule using =A2<TODAY(), replacing A2 with the top-left cell of your selection. Choose a fill color and Excel will update the highlighting as the current date changes. Your dates must be stored as real Excel date values, not text.

What You’ll Need

A worksheet containing the dates you want to check, ideally in one column with a header.
Microsoft Excel for Microsoft 365 on Windows. The same conditional formatting approach is available in current Excel for Mac; ribbon labels can vary slightly.
A fill color or font style to make past dates easy to spot.

You do not need an add-in or a separate helper column. Conditional formatting changes the appearance of cells without changing their contents. The rule uses TODAY(), which returns the current date from your computer’s system clock and updates when Excel recalculates the workbook.

Step-by-Step

Check that the cells contain dates. Click a date and look at the formula bar. A real Excel date is stored as a number, even if it displays in a format such as 3-Nov-2026. If the value is stored as text, Excel may not compare it correctly. You can test a sample in an empty cell with a simple numeric check, or try changing the cell’s number format to General. A real date will display as a serial number; changing the format does not convert text into a date.
Select the cells you want to format. For example, if your dates are in A2:A100, select that range and make sure A2 is the active, top-left cell. Leave the header out of the selection. If dates occupy several columns, select the full date area; the formula must refer to the top-left cell in that selection. When applying the rule to a table that will grow, consider selecting a reasonable number of future rows too.
Create a formula-based conditional formatting rule. With A2:A100 selected and A2 as the first cell, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. With dates in A2:A100 and A2 as the top-left cell, enter =A2<TODAY(). If, for example, TODAY() returns 12-May-2026, a cell containing 8-May-2026 makes the comparison TRUE and qualifies for formatting; a cell containing 12-May-2026 makes it FALSE. The comparison excludes today.
Choose a visible format. Select Format in the rule dialog, then choose a fill color on the Fill tab, a font style or color on the Font tab, or both. A light fill with dark text is often easier to read than a saturated background. Select OK to close the Format Cells dialog, then OK again to save the rule. The selected cells should now show the chosen style wherever their date is earlier than today.
Prevent blank cells from being highlighted. A blank can behave like a zero in a comparison, and zero is earlier than today. If your date column has empty rows, edit the rule so it also checks that a cell contains a number. With dates in A2:A100 and A2 as the top-left cell, use =AND(ISNUMBER(A2),A2<TODAY()). For example, if A2 is blank, ISNUMBER(A2) is FALSE, so the whole AND test is FALSE and the blank is not highlighted. A real date of 8-May-2026 is highlighted when TODAY() is 12-May-2026.
Check the rule’s range and behavior. Select a cell in the date range and open Home > Conditional Formatting > Manage Rules. Set the display to This Worksheet if needed, then check that Applies to covers the intended cells, such as =$A$2:$A$100. Confirm the formula uses the same first row as the range. Test with one date before today, one date equal to today, one future date, and a blank. Remove any temporary test values afterward.

The formula’s row reference should remain relative: A2, not $A$2. Conditional formatting evaluates the rule separately for each cell in the selected range. With A2 as the first cell, Excel evaluates A3 for the next row, A4 for the one after that, and so on. If you lock the row in the formula, every row may be judged against the same date.

On Excel for Mac, select the cells and use Home > Conditional Formatting > New Rule, then choose the formula-based rule option and enter the same formula. The dialog may look different from the Windows dialog, but the relative-reference principle is unchanged. In Excel for the web, the conditional formatting commands are also on the Home tab; if your account’s interface does not offer a formula rule, open the workbook in the desktop app to create or edit it.

Common Mistakes

Using the wrong starting cell in the formula. If your selected range starts at B5, the formula should refer to B5, even if the range extends down many rows. A reference to A2 makes Excel test the wrong cells.
Locking the cell reference unnecessarily. Writing =$A$2<TODAY() makes every selected cell compare A2 with today. Use the relative reference =A2<TODAY() when the rule should evaluate each cell independently.
Expecting today to count as a past date. The less-than sign means strictly earlier than today. If you want to include today, the comparison needs to include equality as well. Decide what “past” means for your workflow before changing the rule.
Applying the rule to the header or unrelated cells. A heading such as “Due date” is text, not a date, and can make the result look inconsistent. Keep the header outside the range and verify Applies to in Manage Rules.
Assuming a date-looking value is a date. Text such as an imported “2026-05-08” may look like a date but fail the comparison. Confirm the cell is a numeric date value before adjusting the formula or color.

Tips

Choose the comparison that matches your purpose. Use earlier than today for dates that have already passed. If you are tracking deadlines and want the current day included, use a less-than-or-equal comparison instead. Try the rule on a few known dates before applying it to a large schedule.
Decide how to treat times in date cells. Excel stores a date and time as a number with a fractional part for the time. With dates and times in A2:A100 and A2 as the top-left cell, =A2<TODAY() marks dates from earlier days as past, but a timestamp from today is later than midnight today and is not marked. That is usually suitable when “past” means before the calendar day.
Use a current-moment comparison when you need it. If each timestamp should be flagged as soon as its time passes, compare it with NOW(). With timestamps in A2:A100 and A2 as the top-left cell, use =AND(ISNUMBER(A2),A2<NOW()). For example, a timestamp of 9:00 a.m. is TRUE after 9:00 a.m. on the same day and FALSE before then. NOW() updates when Excel recalculates, not continuously every second.
Make the rule easy to maintain. Keep dates in a dedicated column and use a clear heading. If you convert the data range to an Excel table with Insert > Table, new rows can inherit formatting, but check the conditional formatting rule’s Applies to range when you add data.
Use a restrained color scheme. A pale red or amber fill can signal an overdue date without obscuring the date itself. If the workbook is printed in grayscale or shared with people who may not distinguish colors easily, combine color with bold text or another clear visual cue.

Troubleshooting

No cells change color: Check that the rule is enabled, the selected cells fall within Applies to, and the formula starts with an equals sign. Confirm that its reference points to the top-left cell of that range. Also verify that the date is a real Excel value and that it is earlier than the current date on your computer.
Dates that look old are not highlighted: The cells may contain text. One clue is that changing the number format to General leaves the displayed text unchanged. Convert the text values to real dates using a method suited to their source and regional date order, then test the rule again. Avoid converting a whole column without checking ambiguous dates such as 03/04/2026, which can mean different days in different regions.
Blank cells are highlighted: Edit the rule to use the ISNUMBER version shown above. This makes the rule require a numeric date before comparing it with today. If cells contain formulas that return an empty string, they may not behave like truly empty cells; the numeric test prevents those text results from being treated as dates.
The wrong rows are highlighted: Open Manage Rules and compare the first cell in Applies to with the cell reference in the formula. For a range of =$A$2:$A$100, the formula should usually begin with A2. Also check that the column reference is not accidentally pointing to another field, such as a status column.
A date from today is highlighted unexpectedly or not at all: Decide whether the cell contains only a date or a date-time value. The TODAY() rule compares against midnight at the start of today; a time later today is greater than that value. Use NOW() if you need a current-time comparison, or use a calendar-day comparison if the time portion should be ignored.
Formatting looks inconsistent: Another conditional formatting rule may have a different color or priority. In Manage Rules, inspect all rules that apply to the same cells and adjust their order or remove rules you no longer need. Also check for manually applied formatting that may make the result harder to interpret.

Frequently Asked Questions

Does the highlighting update automatically tomorrow?
Yes. TODAY() returns the current date when Excel recalculates. If the workbook remains open and the display has not refreshed, press F9 on Windows to recalculate. On Mac, use Fn+F9 on keyboards where the function keys control hardware features, or use the Formulas tab’s calculation command.

How do I highlight dates that are past due, including today?
Edit the rule to use a less-than-or-equal comparison. With dates in A2:A100 and A2 as the top-left cell, use =AND(ISNUMBER(A2),A2<=TODAY()). This includes a date equal to today and excludes blank or text cells.

Can I highlight past dates in a different column based on a date in another column?
Yes. Select the cells you want colored and write the formula using the corresponding date column. For example, if you select B2:B100 and the dates to check are in A2:A100, the rule should refer to A2. Keep the row relative so each row checks its own date.

Can I highlight dates older than a set number of days?
Yes. With dates in A2:A100 and A2 as the top-left cell, use =AND(ISNUMBER(A2),A2<TODAY()-30) to highlight dates more than 30 days before today. The threshold is based on calendar days; change 30 to the number of days you need.

Will this rule change or delete my dates?
No. Conditional formatting changes only how qualifying cells appear. The date values stay in the worksheet, and you can remove or edit the rule through Home > Conditional Formatting > Manage Rules without deleting the underlying data.

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.