Conditional Formatting Dates in Excel: Rules and Formulas

To conditionally format dates in Excel for Microsoft 365 on Windows, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules or New Rule. Built-in date rules can highlight dates such as today or next week; a formula rule can handle custom conditions such as dates overdue or weekends.

How date-based formatting works

Conditional formatting changes a cell’s appearance when its value meets a rule. The value stays the same: Excel applies a fill, font, border, or other format without changing the date or the underlying data. This makes the feature useful for schedules, project trackers, expiry lists, invoices, and attendance records.

Excel stores genuine dates as numbers, with each whole number representing a day in its date system. The number is displayed as a date because the cell has a date format. Conditional formatting evaluates the underlying value, not merely what the cell looks like. A date typed as text may appear correct but fail a date-based rule, which is a common source of confusion.

Rules can be fixed, such as highlighting dates before a specific cutoff, or dynamic, such as highlighting dates that are overdue as of today. Dynamic rules can update when the workbook recalculates, making them more suitable for lists that change over time. Excel normally recalculates date-dependent formulas when the workbook opens or changes, although calculation settings can affect when updates appear.

Use Excel’s built-in date rules

For common time periods, start with Excel’s date-occurring rule. Select the cells containing dates, then choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Choose a period, such as yesterday, today, tomorrow, last week, next week, this month, or last month, select a format, and confirm. The rule evaluates dates relative to the current date rather than a date you manually enter.

Built-in rules are convenient when their time periods match the question you need to answer. For example, applying “Today” to a delivery-date column can make deliveries due today easy to spot. “Next Week” can help with near-term planning, while “Last Month” may help identify recently recorded dates. The exact choices available are shown in the rule dialog.

To highlight dates on one side of a fixed cutoff, use a comparison rule instead. Select the cells and go to Home > Conditional Formatting > Highlight Cells Rules > Less Than or Greater Than. Enter a date in the dialog and choose a format. Enter the date in a form Excel recognizes, or use a cell reference where the dialog permits it. This is useful for a fixed deadline, but unlike a TODAY-based formula, the cutoff will not move automatically.

Today or tomorrow: Use A Date Occurring for a quick, relative highlight.
Before a fixed deadline: Use Less Than and supply the cutoff date.
More complex conditions: Use New Rule with a formula to determine which cells to format.

Write formula rules for custom date conditions

Formula rules are useful when a built-in choice is too broad or when the condition depends on another column. In Windows Excel, select the target range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells to format, choose a format, and select OK. A formula rule does not need to return a date; it needs to return TRUE or FALSE.

For example, suppose task due dates are in A2:A100, with a heading in A1. Select A2:A100 before making the rule. To highlight dates earlier than today while ignoring blank cells, use this formula:

With task due dates in A2:A100 and the rule applied to A2:A100, use =AND($A2<>"",$A2<TODAY()). The rule highlights dates before today and leaves blanks unformatted.

The dollar sign fixes the column reference, while the row number remains relative. As Excel evaluates each row in the selected range, it checks A2 for the first cell, A3 for the next, and so on. If the formula instead refers to $A$2, every row will check the same cell. In general, write the formula for the top-left cell of the range to which the rule applies.

To highlight dates from today through the next seven days, including both endpoints, use a second rule. With due dates in A2:A100 and the rule applied to A2:A100, use =AND($A2>=TODAY(),$A2<=TODAY()+7). A date exactly seven days ahead is included; past dates and dates more than seven days ahead are not.

Examples for schedules and trackers

Here are several practical patterns. For each example, select the stated range and create a formula-based conditional formatting rule. Use a different format for each status if you want a visual key, such as red for overdue and amber for approaching deadlines.

Highlight overdue tasks: With due dates in A2:A100, apply this formula to A2:A100: =AND($A2<>"",$A2<TODAY()). It highlights dates before the current date, but not a date that is today.
Highlight weekend dates: With dates in A2:A100, apply this formula to A2:A100: =AND($A2<>"",WEEKDAY($A2,2)>5). With the return type set to 2, WEEKDAY numbers Monday as 1 and Sunday as 7, so values greater than 5 are Saturday and Sunday.
Highlight dates in the current month: With dates in A2:A100, apply this formula to A2:A100: =AND($A2<>"",MONTH($A2)=MONTH(TODAY()),YEAR($A2)=YEAR(TODAY())). Checking the year as well as the month prevents dates from the same month in another year from matching.
Highlight a fixed quarter: With dates in A2:A100, apply this formula to A2:A100: =AND($A2>=DATE(2026,1,1),$A2<DATE(2026,4,1)). This includes dates from January 1 through March 31, 2026, but excludes April 1. The end boundary is exclusive, which avoids ambiguity about the final day.

If the date is in one column but you want to color an entire row, select the full data area first. For example, with task information in A2:D100 and each row’s due date in column B, apply the rule to A2:D100 and use =AND($B2<>"",$B2<TODAY()). The dollar sign before B keeps the date column fixed, while the row changes for each task.

Choose a rule and format that fit

Use built-in rules for quick, familiar time periods and formula rules when you need precision. A built-in rule is easier to maintain if it already expresses the condition. A formula is more flexible: it can ignore blanks, use a date in another column, define a custom range, or combine requirements. However, a complicated rule can be harder for another person to understand, so keep formulas as simple as possible and give the workbook a clear legend.

Choose formatting that communicates status without making the worksheet difficult to read. A light fill with dark text is often easier to scan than a very strong fill. If several rules apply to the same cells, decide which status should take priority. For example, an overdue date might also fall in the current month. Excel evaluates rules in the order shown in Conditional Formatting > Manage Rules; where available, use Stop If True when a higher-priority rule should prevent lower-priority rules from applying. Inspect the rule order if a cell’s appearance is unexpected.

Manage rules and apply them to changing data

To inspect or edit rules in Windows Excel, choose Home > Conditional Formatting > Manage Rules. The Rules Manager lets you change a formula, format, order, or Applies to range, and it can show rules for the current selection or the worksheet. Check Applies to when extending a list: a correct formula does not help rows that are outside the rule’s range.

If you expect to add rows, consider converting the data range to an Excel table with Insert > Table. Tables expand as rows are added, and formatting behavior can be easier to maintain than a rule applied to a fixed range. Still check the conditional formatting rule’s Applies to range after adding data, especially if rows were inserted or pasted in unusual ways. You can also edit the Applies to field in the Rules Manager.

When copying cells, be alert to copied formatting and duplicated rules. Repeated pasting can create multiple overlapping rules that are difficult to diagnose. Use Manage Rules to remove obsolete duplicates, and verify that the remaining rule covers the intended cells. If you need to remove conditional formatting, select the relevant cells and choose Home > Conditional Formatting > Clear Rules; select whether to clear rules from the selected cells or the whole worksheet.

Common problems and how to fix them

If a rule does not highlight a date that looks right, first check whether Excel recognizes the cell as a date. Change the number format temporarily to General: a genuine date usually displays as a number, while date-like text remains text. You can also test a suspect cell with a simple calculation or convert imported text dates using Excel’s data tools. Regional settings matter: the same typed string can be interpreted differently depending on whether the workbook expects month/day/year or day/month/year.

Blank cells can sometimes behave unexpectedly in comparison rules. Add a nonblank test, such as the empty-string check in the examples, when blanks should remain unformatted. Also check for hidden time values. Excel dates can include a time fraction, so a cell displaying only a date may actually contain a time as well. A test against a date boundary can then behave differently than expected if the stored value includes hours or minutes.

Other common causes include a wrong Applies to range, a formula referencing the wrong column, or a fixed row reference where a relative row was needed. Confirm that the formula is written for the top-left cell of its target range. Then open Manage Rules, verify the range and rule order, and make sure the formula uses the correct column for the date. Use a simple fill color while troubleshooting so the result is easy to see.

Excel on Mac and in a browser

In Excel for Microsoft 365 on Mac, select the cells and use the Home tab’s Conditional Formatting controls to create or manage rules; dialog labels and layout can differ slightly from Windows. Excel for the web also offers conditional formatting, although some editing controls and dialog details may differ from the desktop application. If a workbook’s rule is complex, verify its behavior in the version where the workbook will be maintained rather than assuming every interface presents the same options.

Windows shortcuts for opening the Conditional Formatting menu are context-sensitive and can vary with ribbon configuration, so the reliable route is Home > Conditional Formatting. On Mac, use the Home tab’s Conditional Formatting command rather than relying on a Windows key sequence. The core idea is the same across platforms: select the target cells, create a rule, and confirm the range and formula references.

Frequently Asked Questions

Can conditional formatting automatically highlight today’s date?
Yes. Select the date range and choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring > Today. For a custom rule, compare the cell with TODAY() and add a blank check if needed.

How do I highlight dates that are overdue but not blank?
Create a formula rule that tests both that the date cell is not blank and that its date is earlier than today. Apply it to the date range, and make the formula’s row reference match the first row of that range.

Why does Excel not recognize a date in conditional formatting?
The cell may contain text that only looks like a date, or its value may have been imported under a different regional date convention. Check the cell’s value and convert text dates to real Excel dates before relying on date comparisons.

Can I highlight a whole row based on its due date?
Yes. Select the whole table’s data rows when creating the formula rule, then make the formula refer to the due-date column with a fixed column and a relative row. This lets each row’s format depend on its own date.

Do conditional formatting date rules update automatically?
Rules that use relative periods or TODAY() can update as the current date changes and Excel recalculates. A rule based on a date you typed as a fixed cutoff will not move unless you edit the cutoff or make the rule depend on a cell that changes.

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.