To conditionally format dates in Excel, select the cells, then choose Home > Conditional Formatting and either select a date rule or create a formula-based rule. For example, to highlight overdue dates in B2:B100, use a formula rule with =AND(ISNUMBER(B2),B2<TODAY()); the formatting updates as the date changes.
How Excel interprets dates
Excel stores dates as numbers, with each date represented by a serial value. Time can be stored as a decimal fraction of a date. This is why a cell that looks like a date can be compared with another date in a conditional-formatting formula. The display format controls how the value appears; it does not change the underlying value.
For conditional formatting to work reliably, Excel needs a real date value, not text that merely looks like one. A date entered in a recognizable format is usually converted automatically, but imported data can remain text. Regional settings also affect how Excel interprets ambiguous entries such as 04/05/2026: depending on the locale, that may mean April 5 or May 4.
To check a suspicious entry, select it and look at the formula bar, or change the cell’s number format to General. A real date will display as a number; text generally remains text. Excel’s 1900 and 1904 date systems also assign different serial numbers to dates, a consideration when copying dates between workbooks, though most users do not need to change these settings.
Use Excel’s built-in date rules
For common cases, a built-in rule is the quickest option. Select the date cells, then go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Choose a time period offered in the dialog, such as yesterday, today, tomorrow, or a period such as last week or next month, and select a format.
These rules are useful for a dashboard or schedule where you want to call attention to dates relative to the current date. Excel evaluates relative periods as time passes, so a rule for today will apply to a different date tomorrow without you changing the rule. Exact choices can vary by Excel version or platform.
Other useful built-in options include:
- Highlight Cells Rules > Greater Than or Less Than to compare dates with a date typed in the dialog or referenced in your workflow.
- Highlight Cells Rules > Between to format dates within a fixed range.
- Top/Bottom Rules for ranking numeric values, which are usually less intuitive than a date-specific rule for schedules.
Built-in rules are easy to maintain, but they are less flexible than formulas. If you need to ignore blank cells, highlight an entire row, or compare a date with a different cell, a formula rule is often a better fit.
Build a formula-based date rule
To create a custom rule in Excel for Microsoft 365 on Windows, select the range to format, choose Home > Conditional Formatting > New Rule, and select “Use a formula to determine which cells to format.” Enter a formula that returns TRUE for cells you want to format, choose Format to set the appearance, and confirm the dialogs.
With due dates in B2:B100, the following formula highlights dates earlier than today while ignoring blanks and non-date text. Set the rule’s Applies to range to B2:B100.
With due dates in B2:B100, this formula returns TRUE for a real date before today, such as yesterday, and FALSE for today or a future date: =AND(ISNUMBER(B2),B2<TODAY()).
The reference B2 is deliberately relative. When Excel evaluates the rule for B3, it adjusts the reference to B3. If you instead need to compare every row against a fixed cell, use dollar signs to lock the reference. For example, with dates in B2:B100 and a cutoff date in E1, this rule compares each date to the same cutoff; it highlights a date before the value in E1: =AND(ISNUMBER(B2),B2<$E$1).
When entering a formula in Excel’s rule dialog, use a leading equals sign and the cell reference for the first cell in the selected range. If your selected range starts at row 2, use row 2 in the formula. A mismatch between the first selected row and the formula’s row reference is a frequent source of rules that appear shifted.
Highlight an entire row based on its date
To shade a complete task row according to its due date, select the rows and columns you want formatted, such as A2:D100, and create a formula rule. Assume the due date is in column B. Use a mixed reference: the dollar sign fixes the date column while the row number remains relative.
With due dates in B2:B100 and the row-formatting range A2:D100, this formula highlights each row whose date is before today: =AND(ISNUMBER($B2),$B2<TODAY()). Apply a fill color in the Format dialog. The first row in the selected range is row 2, so the formula starts with row 2 as well.
Without the dollar sign before B, Excel can shift the date column as it evaluates cells across the row. Without a relative row number, every row might be checked against the same date. Mixed references solve both problems: $B locks the column, while 2 adjusts for each row.
Useful date patterns for schedules
Conditional formatting can mark more than overdue items. A common task is highlighting deadlines coming up soon. With due dates in B2:B100, the next formula highlights real dates from today through 30 days from today, inclusive. A date that is already past is not included: =AND(ISNUMBER(B2),B2>=TODAY(),B2<=TODAY()+30).
For a whole-row version of that upcoming-deadline rule, use the same selected range and mixed reference pattern described above, replacing B2 with $B2. The TODAY function updates when Excel recalculates the workbook; it does not permanently mark the date on which the rule was first created.
You can also highlight dates in the current month. With dates in A2:A100, this formula checks both the month and year, so it will not incorrectly treat the same month in a different year as a match: =AND(ISNUMBER(A2),MONTH(A2)=MONTH(TODAY()),YEAR(A2)=YEAR(TODAY())).
For a fixed reporting window, use start and end dates in worksheet cells rather than typing dates into the rule. With event dates in A2:A100, a start date in E1, and an end date in F1, the following formula highlights dates on or between those boundaries: =AND(ISNUMBER(A2),A2>=$E$1,A2<=$F$1). Enter real dates in E1 and F1; formatting those cells as dates makes them easier to read.
Set the range and rule priority carefully
A correct formula can still produce unexpected results if the rule applies to the wrong cells. Open Home > Conditional Formatting > Manage Rules to review a rule’s formula, format, and Applies to range. If you add rows later, check whether the range expanded to include them. An Excel Table often makes it easier to work with data that grows, but verify that the conditional-formatting rule covers newly added rows.
More than one rule can apply to the same cell. When their formats conflict, rule order and the Stop If True setting can affect what appears. In Manage Rules, move rules up or down to change their priority, and use Stop If True only when you want lower-priority rules not to be evaluated after a rule matches. Keep overlapping rules simple, and test a row that should match each one.
Common problems and how to fix them
- Nothing is highlighted: Check that the cells contain numeric date values rather than text. Confirm the Applies to range and make sure the formula references the first cell in that range.
- The wrong rows are highlighted: For a whole-row rule, lock the date column with a dollar sign but leave the row number relative, such as $B2. Make sure the selected range begins on the same row used in the formula.
- Blank cells are highlighted: Add a check such as ISNUMBER(B2) to the formula. This excludes blanks and most text values from a date comparison.
- Dates imported as text do not match: Convert the source values to real Excel dates before relying on date comparisons. Depending on the data, Text to Columns or a suitable conversion formula may help; inspect a few converted results before applying the rule to an entire dataset.
- A date appears to be one day off: Check whether the value includes a time. A cell formatted to show only the date may still contain a time fraction, which can affect comparisons near midnight or at an exact boundary.
- The rule changes unexpectedly: TODAY is a volatile, current-date function, so rules based on it move forward as time passes. Use a fixed date in a cell if the rule must remain tied to a particular reporting date.
Choosing a method and keeping rules maintainable
Use a built-in date rule when the choice it offers matches the task and you want a quick setup. Use a formula rule when you need to handle blanks, use a cutoff stored in a cell, check a rolling window, or format an entire row. For rules that colleagues will maintain, place cutoff dates in clearly labeled cells and use descriptive headings such as Due date or Review by.
Conditional formatting changes how cells look, not the date values themselves. It does not sort records, prevent someone from entering an invalid date, or replace a reminder system. If readers need an accessible signal, do not rely on color alone: use a clear column label or status text as well. On Excel for Mac, the Conditional Formatting commands are also on the Home tab, though dialog layouts can differ. Excel for the web supports conditional formatting, but some advanced editing options may be more limited than in the desktop app.
Frequently Asked Questions
Can Excel highlight dates that are overdue automatically?
Yes. Create a formula rule that compares each date with TODAY, and use ISNUMBER to avoid treating blanks or text as dates. The rule updates as the current date changes and the workbook recalculates.
How do I highlight dates within the next 7 days?
Use a formula rule that checks whether a date is at least today and no later than today plus 7. Include ISNUMBER in the rule to ignore blank or text cells. If you want to include overdue dates too, remove the lower-bound check.
Why does conditional formatting not recognize my dates?
The cells may contain text that resembles dates, or Excel may have interpreted the entries using a different regional date order. Convert them to real date values, then test the rule on a few cells and confirm the date range in Manage Rules.
Can I highlight a whole row based on one date cell?
Yes. Select the full row range and create a formula rule that refers to the date column with a locked column and a relative row, such as $B2 when the first selected row is row 2.
Will a TODAY-based rule update tomorrow?
It is designed to evaluate against the current date, so it can show different results on a later day when Excel recalculates. If a workbook is not recalculating as expected, check its calculation settings or reopen it.
Related
- Bps Number Format in Excel: Display Basis Points
- Bottom Border Excel Shortcut and How to Use It
- Billion Number Format in Excel: Display Values in Billions
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Schedule an Email in Outlook on Any DeviceTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send or…
- Import Multiple Files into Excel: A Practical GuideYes. In Microsoft Excel for Microsoft 365 on Windows, use Data > Get Data > From File >…
- Excel or Word for a Checklist? Compare the Best Fit for YouChoose Excel for a checklist you need to sort, filter, update, or track across many tasks; choose Word…
- Excel Conditional Formatting for Expiry DatesTo highlight expiry dates in Excel, select the date cells, choose Home > Conditional Formatting > New Rule…
- Large Excel File Won’t Open? Causes and Practical FixesIf a large Excel file won’t open, first copy it to a local drive, close other programs, and…
- Auto Loan Amortization Schedule in Google SheetsYes—Google Sheets can create an auto loan amortization schedule that shows each monthly payment’s interest, principal, and remaining…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.