Excel Conditional Formatting for Past Dates: How to Set It Up

How-to basics · Updated November 22, 2026

Yes, you can highlight past dates in Excel using conditional formatting with a formula like =A2<TODAY(). This rule marks any date earlier than the current day, and it updates automatically each time the workbook recalculates.

Why Conditional Formatting for Dates Is Useful

Tracking deadlines, expiry dates, or payment due dates is a common spreadsheet task. Manually scanning a column of dates to spot which ones have already passed is error-prone and time-consuming. Conditional formatting automates this: it applies a fill color, font color, or border to cells that meet a condition you define. Because the condition can reference the current date, the formatting stays current without any manual updates.

For example, a project manager might want to instantly see which task deadlines have slipped. A warehouse manager might flag perishable items whose sell-by date has passed. In each case, a simple rule can turn a static list into a live dashboard.

How to Create a Basic Past-Date Rule

The quickest way to highlight dates before today is to use a built-in rule, but the formula approach gives you more control. Here are both methods.

Method 1: Built-in “A Date Occurring” Rule

  1. Select the range containing your dates (for example, A2:A100).
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
  3. In the dialog, choose “before” from the first drop-down and “today” from the second.
  4. Pick a formatting style (like “Light Red Fill with Dark Red Text”) and click OK.

This rule highlights dates that are strictly before today. It does not highlight today’s date itself. If you want to include today, use the formula method below.

Method 2: Formula-Based Rule

Using a formula gives you flexibility, such as including today or ignoring blank cells.

  1. Select the range (e.g., A2:A100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format”.
  4. Enter =AND(A2<>"", A2<TODAY()) to highlight only non-blank past dates.
  5. Click “Format…”, choose a fill color, and click OK twice.

The formula uses the active cell (the first cell in the selected range) as the reference. If your range starts at A2, the formula should reference A2. Excel adjusts the reference for each row automatically.

Including Today’s Date or Using Other Comparisons

Sometimes “passed” means “today or earlier.” To include today, change the formula to =AND(A2<>"", A2<=TODAY()). If you want to highlight dates that are more than 30 days past, use =AND(A2<>"", A2<TODAY()-30). For dates that are within the next 7 days (upcoming deadlines), use =AND(A2<>"", A2>=TODAY(), A2<=TODAY()+7).

You can combine these with other functions like EDATE or EOMONTH for more complex scenarios, such as highlighting dates that are past the end of the current month.

Common Problems and How to Fix Them

Several issues often arise when applying date formatting.

  • Dates not formatting: Ensure your cells contain real dates, not text. You can test by selecting a cell and checking the Number Format dropdown—if it shows “Date”, it’s a true date. Text dates won’t work with TODAY().
  • Rule applies to wrong cells: Check the “Applies to” range in the Conditional Formatting Rules Manager. It should match your selected range.
  • Rule order conflicts: If you have multiple rules, Excel applies them in order. Use “Stop If True” to prevent lower rules from overriding your past-date highlight.
  • Today’s date not highlighted: The built-in “before today” rule excludes today. Use the formula with <= to include it.
  • Blank cells highlighted: In Excel, blank cells are treated as 0, which is less than today’s date. The <>"" condition prevents this.

Using Conditional Formatting with Other Functions

You can make your past-date rule smarter by combining it with other functions. For instance, to highlight overdue tasks only if the status column says “Pending”, use a formula like =AND($A2<TODAY(), $C2="Pending") where column C contains status. This is useful in project trackers.

Another example: highlight dates that are past and also fall on a weekend. Use =AND(A2<TODAY(), OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)). The WEEKDAY function returns 1 for Sunday and 7 for Saturday.

Managing and Clearing Rules

To see all rules in a sheet, go to Home > Conditional Formatting > Manage Rules. From there you can edit, delete, or change the order of rules. To remove all formatting from a range, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Be careful: clearing rules is not undoable with Ctrl+Z, so consider saving a copy of your workbook first.

Frequently Asked Questions

Why isn’t my date formatting updating automatically?
Conditional formatting recalculates when Excel recalculates the workbook. If you have manual calculation mode (Formulas > Calculation Options > Manual), press F9 to recalculate. Otherwise, it updates on open and when you edit cells.

Can I highlight past dates in Google Sheets the same way?
Yes, the formula approach works similarly. In Google Sheets, go to Format > Conditional formatting, choose “Custom formula is”, and enter =A2<TODAY(). The main difference is the menu path and the lack of the “A Date Occurring” preset.

How do I highlight a whole row based on a past date?
Select the entire data range (e.g., A2:F100), then create a formula rule with an absolute column reference: =$A2<TODAY(). The dollar sign locks the column, so the rule evaluates column A for each row and formats the entire row.

What if my dates are in a different time zone?
Excel stores dates as serial numbers based on your system’s date and time settings. If you are collaborating across time zones, the “today” value may differ. To avoid confusion, ensure all users have the same date system (1900 vs. 1904) by checking File > Options > Advanced > “Use 1904 date system”.

Can I use conditional formatting to highlight expired dates in a list of subscriptions?
Absolutely. Use a formula like =AND(B2<TODAY(), C2="Active") to highlight only active subscriptions that have expired. This helps you focus on renewals.

Final Thoughts

Conditional formatting for past dates is a simple yet powerful feature that saves time and reduces errors. Start with the built-in rule for quick results, then move to formulas when you need more control. Remember to test your rule on a small sample first, and always keep a backup of important workbooks before making sweeping changes.

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.