Excel date conditional formatting automatically changes a cell’s appearance when its date meets a condition. Select the date cells, then choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring for built-in options, or use New Rule to create a custom formula-based rule.
How date conditional formatting works
Conditional formatting applies a visual format—such as a fill color, font color, or border—when a cell meets a test. It does not change the cell’s underlying value. Excel stores recognized dates as serial numbers, so a date comparison is generally a comparison of numbers. For example, a date later in the calendar is represented by a larger serial value than an earlier date.
When you use a built-in date rule, Excel evaluates the selected cells against the current date or a calendar period. A rule such as “Today” is therefore dynamic: a cell that is not highlighted today may be highlighted when the date arrives, then stop matching the rule the next day. Custom formula rules work similarly when they refer to functions such as TODAY().
Conditional formatting is useful for deadline lists, appointment schedules, project plans, renewal dates, and inventory records. It can make important dates easier to scan, but it does not replace sorting, filtering, or a reminder system. A colored cell is a visual cue, not a notification.
Use Excel’s built-in date rules
For common date checks, start with the built-in rules rather than writing a formula. In Excel for Microsoft 365 on Windows:
- Select the cells containing dates, such as A2:A100. Avoid selecting a whole column unless you need to format future entries too.
- Choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
- Choose a period, such as Yesterday, Today, Tomorrow, In the Last 7 Days, Last Week, This Week, Next Week, Last Month, This Month, or Next Month.
- Choose one of the offered formats, or select Custom Format to specify a fill, font, or border, then confirm the rule.
The exact wording or arrangement can vary slightly with the Excel interface, but the date-occurrence rule is designed for these common calendar periods. If you want a fixed interval rather than a relative period, use Home > Conditional Formatting > Highlight Cells Rules > Between and enter the two endpoints. A fixed date interval will not move forward automatically; it continues to use the values entered in the rule.
On Excel for Mac, the conditional formatting commands are available from the Home tab as well, although dialogs can look different. Excel for the web also includes conditional formatting controls; if a rule’s advanced settings are difficult to edit there, open the workbook in the desktop app.
Create custom rules with formulas
A formula rule is useful when the built-in date periods do not express the condition you need. Select the range first, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells you want formatted, choose the format, and confirm. In formulas below, the assumed range is stated before each example; the formula’s row reference should match the first row of the selected range.
With dates in A2:A100 and A2 as the first selected cell, use =AND($A2<>"",$A2<TODAY()) to highlight dates before today. The AND test excludes blank cells, and a date equal to today is not treated as overdue. This is appropriate for a list where a deadline is overdue only after its due date has passed.
With dates in A2:A100 and A2 as the first selected cell, use =AND($A2<>"",$A2=TODAY()) to highlight dates that fall today. The dollar sign fixes the column while leaving the row relative, so Excel checks A2, A3, A4, and so on as it evaluates the range.
With dates in A2:A100 and A2 as the first selected cell, use =AND($A2>=TODAY(),$A2<=TODAY()+7) to highlight dates from today through seven days from today, inclusive. Because both endpoints are included, this window can contain eight calendar dates: today plus the following seven. If you mean the next seven dates excluding today, use a start condition greater than TODAY() instead.
These formulas assume Excel recognizes the cell values as dates. They also assume the selected range begins on row 2. If the selection begins on row 5, adjust the formula’s row reference to 5; otherwise, the formatting can be offset by a row.
Format an entire row based on its date
In a task table, you may want the whole record highlighted when its due date is late. Suppose the due dates are in column C, and the table occupies A2:F100. Select A2:F100, create a formula rule, and use a formula that checks the date column while allowing each row to change.
With due dates in C2:C100, the table in A2:F100, and A2 as the selection’s top-left cell, use =AND($C2<>"",$C2<TODAY()). The dollar sign before C locks the date column; the row number remains relative. Excel therefore checks C2 when formatting row 2, C3 for row 3, and so forth. If the formula instead uses C2 without fixing the column, Excel may shift the reference as it evaluates cells across the row.
For a formula rule, the reference is interpreted relative to the top-left cell of the range in Applies to. If you later change that range, check that the formula still points to the intended first row. This is one of the most common reasons a correct-looking formula colors the wrong records.
Choose the right date window
Decide whether the condition should move with the calendar or stay fixed. Built-in periods such as Today and This Month, and formulas using TODAY(), are rolling conditions. They are helpful for live schedules. A manually entered start and end date, by contrast, is a fixed condition suited to a defined reporting period or a one-time review.
Calendar periods and day counts are not interchangeable. “Next month” means the next calendar month, whereas the next 30 days is a moving day-count window. For a custom next-calendar-month rule, you can compare against the first and last dates of that month. With dates in A2:A100 and A2 as the first selected cell, use =AND($A2>=EOMONTH(TODAY(),0)+1,$A2<=EOMONTH(TODAY(),1)). EOMONTH returns the last day of a month; adding one to the current month’s end gives the first day of next month. The rule includes both ends of that calendar month.
Choose the condition based on how people will use the sheet. A project manager might want overdue items in red and items due within a week in amber. A monthly report might instead need a color for dates in the reporting month, which should be based on that month’s fixed start and end rather than on the day the file is opened.
Dates with times and other common problems
A cell can contain both a date and a time, even if its number format displays only the date. An exact test such as A2=TODAY() may not match a value that represents today at 9:00 a.m., because that value includes a fractional part for the time. For a date-and-time list, test whether the value falls from the start of today up to, but not including, tomorrow.
With date-and-time values in A2:A100 and A2 as the first selected cell, use =AND($A2>=TODAY(),$A2<TODAY()+1) to highlight every timestamp today. The upper bound is exclusive, so a value exactly at midnight tomorrow is not included.
If an expected cell does not format, check the underlying value before changing the rule. A date that was imported as text may look like a date but will not behave like a normal Excel date in comparisons. Try changing the number format to a date or inspect the cell in the formula bar; changing the display format alone does not convert text into a date. Text-to-date conversion may require correcting the source data or using Excel’s conversion tools.
- Unexpected blanks highlighted: add a nonblank check such as A2<>"" to a custom formula.
- Wrong rows highlighted: make the formula row number match the first row of the Applies to range.
- Rule seems one day early or late: check for hidden time values and whether the range’s endpoints are inclusive.
- Colors differ from expectations: review overlapping rules and their order in the Rules Manager.
- Dates display differently across computers: check whether regional date-entry conventions caused a value to be interpreted as a different date.
Manage, edit, and remove rules
To review existing rules, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, select the relevant worksheet or current selection in the display control, then inspect the rule description and its Applies to range. A rule can be logically correct but affect the wrong cells if that range is incomplete or too broad.
When two rules apply to the same cell, more than one format can influence the result. Rule order matters, and the Stop If True option can prevent lower-priority rules from being evaluated when a higher rule matches. For example, a specific overdue rule may need priority over a general rule that colors all dates in the current month. Test the result on a few known dates after editing the order.
Use the Manage Rules dialog to edit a formula or format, change the applicable cells, reorder rules, or delete a rule. If a color remains after you think a rule is removed, check for another rule covering that cell and for ordinary cell formatting applied directly to it.
Frequently Asked Questions
How do I highlight dates that are overdue in Excel?
For a quick setup, select the date range and choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring if an available period fits. For a custom overdue condition that excludes today and blanks, use a formula rule such as =AND($A2<>"",$A2<TODAY()), adjusting the reference to the first cell in your range.
Will conditional formatting update automatically every day?
Rules based on TODAY() and built-in relative date periods are designed to update as the current date changes, when Excel recalculates the workbook. They do not send alerts. If the workbook’s calculation setting is Manual, recalculation may be needed before displayed results refresh.
Can I highlight a whole row when a date is due?
Yes. Select the complete table range, create a formula rule, and refer to the date column with a fixed column and relative row, such as $C2. Make the formula’s row match the first row of the selected table range.
Why does the Today rule miss a date that includes a time?
An exact comparison with today’s date can fail when the cell contains a time fraction. Use a lower limit of TODAY() and an upper limit of TODAY()+1, with the upper comparison set to less than, so every time today is included.
Does conditional formatting change the date or sort the list?
No. It changes only the visual appearance of matching cells. The stored values remain the same, and the rule does not reorder rows; use Excel’s sort and filter commands separately when you need to organize the data.
Related
- Contact List Format in Excel: Columns, Setup, and Tips
- Contact List Excel Sheet: How to Build and Use One
- Contact List Excel Spreadsheet: Create and Manage It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Keyboard Shortcut for Inserting Copied CellsIn Excel for Microsoft 365 on Windows, copy the cells with Ctrl+C, select the cell where the copied…
- Excel Mac: Change the Default Date Format for New WorkbooksTo change Excel’s default date format on a Mac, first adjust the date format in macOS under System…
- Excel Name Box: Change a Range or Named RangeTo select a different cell range, click the Name Box to the left of the formula bar, type…
- How to Open XML Files in Excel and Import Their DataTo open an XML file in Excel for Microsoft 365 on Windows, use Data > Get Data >…
- Repeating Header in Word: How to Add One to Every PageTo add a repeating page header in Word, open Insert > Header, choose a layout, and enter the…
- Google Sheets Dropdown From a Table: How It WorksYes. In Google Sheets, create a dropdown from table data by selecting the cells that need the menu,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.