To conditionally format dates in Excel, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring to highlight common periods such as today or next week. For custom date logic, create a formula rule—for example, with dates in B2:B100, use =AND($B2<TODAY(),$B2<>"") to highlight past dates while leaving blanks unformatted.
How date conditional formatting works
Conditional formatting changes a cell’s appearance when its value meets a condition. It does not change the underlying date, replace a formula, or permanently mark a record. Excel evaluates the rule and applies the chosen format—such as a fill color, font color, or border—to cells that qualify. If the values later change, Excel reevaluates the rule.
Excel stores dates as serial numbers, with each whole number representing a day. A cell that looks like “November 3, 2026” may therefore contain a numeric value that Excel can compare with another date. This is why rules based on “before,” “after,” or a date range generally work when the cells contain real Excel dates, even if their display format is different.
The appearance of a value alone does not prove it is a date. Imported data may contain text that looks like a date, such as a string copied from a website. A text value may not behave as expected in date comparisons. If a rule does not work, first check that Excel recognizes the entries as dates rather than text.
Use Excel’s built-in date rules
For routine time periods, the built-in date rule is usually the quickest option. Select the cells to format, then go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Choose a period from the dialog and select a format, or choose Custom Format to specify one. Available period choices include options such as Yesterday, Today, Tomorrow, In the last 7 days, This week, Next week, This month, and Next month.
The rule is useful for dashboards and schedules where a relative period matters more than a specific calendar date. For instance, applying “Today” to a due-date column makes the matching entry stand out. Because the condition is relative, the highlighted date changes as the calendar advances; you do not need to edit the rule each day.
- Today or tomorrow: identify items requiring immediate attention.
- In the last 7 days: flag recent dates, such as recently completed work.
- This month or next month: make near-term milestones easier to scan.
- Last week or next week: highlight a weekly planning window.
Built-in choices are convenient, but they may not match a business definition of a period. “Next seven days,” for example, can mean today through six days from now, or the seven full days after today. A custom formula makes the boundary explicit and gives more control over blanks, times, and associated row data.
Create a custom date formula rule
To use a formula, select the range that should receive formatting, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for the cells to format, choose Format, set the appearance, and select OK. In the rule, reference the first row of the selected range; Excel adjusts relative references for the other rows.
With due dates in B2:B100, use =AND($B2<TODAY(),$B2<>"") to highlight dates before today and exclude empty cells. For an example where today is November 3, 2026, a valid date of November 2, 2026, qualifies, while November 3 and a blank do not. The dollar sign fixes the column B reference, while the row number remains relative so each row is checked against its own date.
With dates in B2:B100, use =AND($B2>=TODAY(),$B2<=TODAY()+7) to highlight dates from today through seven days from today, inclusive. If today is November 3, 2026, dates from November 3 through November 10 qualify. The rule does not include November 11. If you instead mean the next seven complete days, excluding today, adjust the lower boundary to tomorrow.
With dates in B2:B100, use =AND($B2>=EOMONTH(TODAY(),-1)+1,$B2<=EOMONTH(TODAY(),0)) to highlight dates in the current calendar month. EOMONTH returns the last day of a month; adding one to the previous month’s end gives the first day of this month. This handles months of different lengths without manually changing the endpoints.
With dates in B2:B100, use =$B2=DATE(2026,11,15) to highlight November 15, 2026. DATE constructs a date from year, month, and day numbers, avoiding confusion from regional date-entry conventions. Replace the numbers to target another date. If the cells also contain times, an exact equality comparison may not match; use a range from the target date through the instant before the following day instead.
Format an entire row based on its date
A useful variation is to color the whole record when its date meets a condition. Suppose each task occupies columns A through F, with its due date in column B and records beginning on row 2. Select A2:F100, create a formula-based rule, and enter =AND($B2<TODAY(),$B2<>""). The dollar sign before B keeps the rule looking at the due-date column, while the row number changes for each task. Excel then formats the selected row range wherever the date in column B is overdue.
For a row rule, check both the formula and the “Applies to” range in Conditional Formatting Rules Manager. If the selected range starts at row 2 but the formula refers to B1, each record will be evaluated against the wrong row. Likewise, removing the dollar sign from the column reference can cause the reference to shift as the format is applied across columns. Mixed references such as $B2 are designed for this common pattern.
Dates with times, blanks, and text
A date-time value includes a fractional part for the time. For example, a value for November 3 at noon is greater than the serial value for November 3 at midnight. This means an exact comparison to TODAY() may not match every timestamp on today’s date. To identify anything from today, use a start-of-day boundary and an exclusive next-day boundary.
With date-time values in B2:B100, use =AND($B2>=TODAY(),$B2<TODAY()+1) to highlight all times today. If today is November 3, 2026, a value at 9 a.m. that day qualifies, while midnight on November 4 does not. The less-than comparison at the upper boundary avoids including the next day.
Blank cells need deliberate handling. In some comparisons, an empty cell can behave like zero, which is earlier than a modern date. Adding a nonblank test such as $B2<>"" prevents empty cells from being marked as overdue. If imported dates appear as text, convert them to actual dates before relying on date comparisons. Depending on the data, options include using Text to Columns, a date-aware import process, or a conversion formula; confirm the converted result is numeric and displays as a date.
Manage rules and resolve common problems
Use Home > Conditional Formatting > Manage Rules to inspect existing rules, change their order, edit formats, or adjust the ranges they apply to. The “Show formatting rules for” list lets you view rules for the current selection or worksheet. When several rules apply to one cell, order can matter, especially if rules specify conflicting formats. “Stop If True” can prevent lower-priority rules from being evaluated after a matching rule, but it should be used only when that is the intended behavior.
- Nothing is highlighted: confirm the rule’s “Applies to” range includes the cells you expect, and verify that the first-row reference in the formula matches the range’s first row.
- Every row looks the same: check for an accidentally fixed row reference such as $B$2 when each row should test its own date. For row-by-row rules, $B2 is typically appropriate.
- Past dates include blanks: add an explicit nonblank condition to the formula.
- Some imported dates fail: check whether those entries are text, or whether they contain times that alter the comparison.
- A rule seems stale: TODAY() updates when Excel recalculates the workbook. Check that calculation is not set to Manual under Formulas > Calculation Options.
TODAY() is a volatile function: it can update when Excel recalculates, so a date-based highlight can change when the workbook is opened or recalculated. This is helpful for live schedules, but not for a historical record that must preserve the status as it was on a past date. For an audit snapshot, consider recording a fixed date or status rather than relying solely on a moving rule.
Conditional formatting versus date filters and formulas
Conditional formatting is best when the goal is visual emphasis while keeping all records visible. A filter is better when the goal is to show only a subset, such as overdue tasks. A worksheet formula is appropriate when the result needs to be displayed as text or used in another calculation—for example, a separate “Overdue” status column. These tools can also be combined: a helper column can define a business rule, and conditional formatting can make the matching rows easier to spot.
Formatting does not sort records, change their values, or guarantee that users will notice an alert. It is a visual cue, not a substitute for a notification or a data-validation system. If a deadline is operationally important, use a clear status field and an appropriate workflow in addition to color, and avoid relying on color alone to communicate meaning.
Frequently Asked Questions
Can Excel highlight dates automatically when they become overdue?
Yes. Apply a formula rule to the date range using a comparison with TODAY(), and include a nonblank test so empty cells are ignored. The format updates when Excel recalculates the workbook.
How do I highlight dates within the next 30 days?
With dates in B2:B100, use =AND($B2>=TODAY(),$B2<=TODAY()+30) in a formula-based conditional-formatting rule. This includes today and the date 30 days from today; change the lower boundary if you intend to exclude today.
Can I highlight a whole row based on one date column?
Yes. Select the full row range you want formatted, such as A2:F100, and write the formula to reference the date column with a fixed column and relative row, such as $B2. The rule then checks column B for each row and applies the format across the selected columns.
Why does conditional formatting not recognize my dates?
The entries may be text rather than Excel date values, or they may include times that make an exact comparison fail. Check the cell values and convert text dates to real dates; for date-times, use a start-to-end range rather than equality with a date-only value.
Does a date rule update tomorrow?
Rules based on TODAY() are recalculated as Excel recalculates the workbook, so the qualifying dates can shift with the calendar. If you need a fixed historical result, store a fixed date or status instead of depending on a moving TODAY() condition.
Related
- Excel Color Scales: How Conditional Formatting Works
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS Function for Data Analysis: Rules and ExamplesExcel’s IFS function tests conditions in order and returns the result for the first condition that is TRUE.…
- Remove Spaces in Excel Rows: Trim, Find, and ReplaceTo clean spaces at the start or end of text, or reduce repeated spaces between words, use Excel’s…
- Excel Filter With Multiple Criteria: A Practical GuideTo filter a worksheet by more than one condition in Excel for Microsoft 365 on Windows, select your…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
- Concat Excel Multiple Cells: Formulas, Spaces, and TipsTo concat (combine) multiple cells in Excel, use =CONCAT(A2:C2) to join the values in A2 through C2 without…
- Excel SUM and AVERAGE: Formulas, Examples, and TipsIn Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.