Excel Conditional Formatting for Expiry Dates

How-to basics · Updated November 19, 2026

To highlight expiry dates in Excel, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a rule such as =AND($D2<>"",$D2<TODAY()) for dates before today. Add a second rule for dates expiring within 30 days, choose a different fill color, and replace column D or the row number to match your sheet.

Set up basic expired and upcoming rules

Assume column D contains expiry dates, with the first record in row 2. Select D2:D100 before creating either rule. In the New Formatting Rule dialog, choose “Use a formula to determine which cells to format,” enter the formula, select Format to choose a fill or font color, and confirm. The dollar sign fixes the date column while leaving the row number relative, so Excel evaluates D2 for the first cell, D3 for the next, and so on.

For an expired-date rule that ignores empty cells, use this formula with expiry dates in D2:D100:

=AND($D2<>"",$D2<TODAY())

Set its format to red, for example. The formula marks a date earlier than today; it does not mark an expiry date of today as expired. That distinction matters when an item remains valid through its printed expiry date.

For a second rule that marks dates from today through the next 30 days, use this formula with the same D2:D100 selection:

=AND($D2>=TODAY(),$D2<=TODAY()+30)

Choose an amber or yellow format to distinguish “expiring soon” from already expired. The 30-day window includes both today and the date 30 days from today. To use a different warning period, replace 30 with another number, such as 14 or 60. These rules can be created through Home > Conditional Formatting > New Rule in Excel for Microsoft 365 on Windows. Excel for Mac has the same conditional-formatting rule concept; ribbon placement and dialog appearance can differ slightly. Excel for the web also supports formula-based conditional formatting, though some advanced rule-management options vary by version.

Confirm that Excel recognizes the dates

Conditional formatting compares actual date values, not merely text that looks like a date. Excel stores dates as serial numbers, which lets it compare a cell with TODAY(). If an imported value such as “2026-11-20” is text, the rule may not behave as expected, even though the cell looks date-like. Date formats such as 11/20/2026 can also be ambiguous when a file is moved between regions that use different month-day conventions.

To check a suspect cell, select it and inspect the formula bar and number format. You can temporarily change the cell format to General: a genuine Excel date will display as a number. Formatting a text value as Date does not convert the text into a date. Convert imported values using a suitable method for their source format, such as Data > Text to Columns when the values can be parsed reliably, or a helper formula tailored to the text pattern. Check several converted results before applying the rule to a large list.

Also check for stray spaces, errors, and inconsistent entries. A column containing dates alongside labels such as “pending” cannot be evaluated uniformly. It is often clearer to keep status text in a separate column and use a date column only for actual dates. If a formula-based rule seems to color the wrong records, verify that the selected range begins on the same row used in the formula. A formula beginning with $D2 is appropriate for a range starting in row 2; if the selected range starts at row 5, use $D5 instead.

Decide what “expired” means for your workflow

There is no universal rule for whether an item expires at the start or end of the date printed on its label. A date-only rule using less-than TODAY() treats the item as valid through today and expired starting tomorrow. That convention is often suitable for stock, memberships, documents, or certificates whose date is inclusive. If your organization treats the printed date itself as expired, change the comparison to less than or equal to TODAY().

For a list with expiry dates in D2:D100, the inclusive interpretation—flagging today as expired—uses:

=AND($D2<>"",$D2<=TODAY())

Then make the upcoming rule start tomorrow so that today is not marked by both rules. For the same range, an upcoming window of 30 days that excludes today uses:

=AND($D2>TODAY(),$D2<=TODAY()+30)

For example, if today is November 3, 2026, a November 2 expiry is expired under the first convention, November 3 falls in the “due today” or inclusive-expired category depending on your policy, and November 20 is 17 days away. A shared workbook should state the chosen convention in a note or header. This prevents one person from interpreting yellow as “still valid today” while another assumes the date is already past its deadline.

Highlight the entire record, not only the date

When a table includes a product name, lot number, owner, and expiry date, coloring only the date cell may make overdue items hard to find. To format whole rows, select the complete data area—for example A2:F100—and create a formula rule that refers to the expiry-date column. With expiry dates in D2:D100 and the selection beginning in row 2, use:

=AND($D2<>"",$D2<TODAY())

Apply a subtle fill to the row. The dollar sign before D keeps the test in the expiry column as the formatting moves across columns A through F; the row number remains relative so each record is tested against its own date. For a second whole-row rule for the next 30 days, use:

=AND($D2>=TODAY(),$D2<=TODAY()+30)

Use a lighter warning color for that rule so text remains readable. If you insert columns or move the expiry field, review the formula and the Applies to range. Open Home > Conditional Formatting > Manage Rules to inspect rules, their order, and the cells they affect. In the dialog, choose the current worksheet in the “Show formatting rules for” list if the rule you want is not visible.

Choose a warning window that people can act on

A useful warning period depends on how long it takes to respond. A 30-day warning may give a team time to reorder supplies, renew credentials, or schedule an inspection; a seven-day warning may suit a task that can be completed quickly. Consider the volume of records too: a short window can keep a large list focused, while a longer lead time can help with items that require shipping or approval.

You can create separate bands rather than using one broad warning color. For example, with expiry dates in D2:D100, a rule for dates 1–7 days away is:

=AND($D2>=TODAY(),$D2<=TODAY()+7)

With the same layout, a second rule for dates 8–30 days away is:

=AND($D2>TODAY()+7,$D2<=TODAY()+30)

Use distinct but legible formats, such as a strong warning color for the nearest dates and a softer color for the later band. These ranges do not overlap, and the second excludes dates through day 7. This makes the categories easier to interpret than multiple rules that all match the same cells. Avoid relying on color alone if the sheet is printed in grayscale or used by people with color-vision differences; add a status column, text label, or clear legend where appropriate.

Understand TODAY, recalculation, and date-time values

TODAY() returns the current date according to Excel’s calculation environment, without a time component. Because it is a volatile function, Excel recalculates it when the workbook recalculates. It is not a permanent timestamp and does not record the date when a row was entered. A workbook left open may not visibly update at midnight until Excel recalculates. If dates appear stale, check Formulas > Calculation Options > Automatic, then recalculate the workbook.

If the expiry cells contain times as well as dates, the result can differ from a date-only comparison. TODAY() represents midnight at the start of the current day. A timestamp later today is therefore greater than TODAY(), even though it shares today’s calendar date. For a rule that treats every timestamp on today’s date as due today, compare calendar dates rather than the full timestamp. With expiry date-times in D2:D100, a formula for dates earlier than today is:

=AND($D2<>"",INT($D2)<TODAY())

INT removes the fractional time portion from a valid Excel date-time serial. If exact deadlines matter, such as an expiration at a particular hour, use a date-time threshold that reflects the actual policy rather than discarding the time.

Fix common conditional-formatting problems

  • Blank cells turn red: Add the nonblank check, such as $D2<>””, to the rule. Empty cells can otherwise be treated unexpectedly in comparisons.
  • The wrong row is colored: Match the formula’s starting row to the first row in the Applies to range. For A2:F100, use row 2 in the formula, not row 1.
  • Every row follows the first date: Use a relative row reference such as $D2, not $D$2. The latter locks both the column and row.
  • Nothing changes: Check that cells contain numeric Excel dates, that the formula has a leading equals sign, and that the rule applies to the intended range. Also check for a conflicting rule or a format that is hard to see.
  • Colors conflict: Review Home > Conditional Formatting > Manage Rules. Adjust the rule order or use nonoverlapping formulas. “Stop If True” is available for rules in desktop Excel, but it is usually unnecessary when categories are designed to be mutually exclusive.

When checking a formula, remember that conditional formatting formulas are evaluated relative to the top-left cell of the Applies to range. An incorrect anchor can make a rule look syntactically valid but evaluate the wrong record. If the workbook has many rules, remove obsolete rules carefully rather than clearing all formatting; clearing formats can also remove useful number formats and visual distinctions.

Use a status column when the sheet needs more than color

Conditional formatting is a visual signal; it does not change the date, block an action, or send a notification. For workflows that need searchable statuses or counts, add a status column alongside the visual rules. With expiry dates in D2:D100 and a status formula entered in E2 and filled down, use:

=IF(D2="","",IF(D2<TODAY(),"Expired",IF(D2<=TODAY()+30,"Expiring soon","Current")))

For a date of November 20, 2026, when today is November 3, 2026, this formula returns “Expiring soon” because the date is 17 days away. A blank in D2 returns a blank status. This is a useful example of a formula result, but the displayed wording and the 30-day threshold should match the rules used for conditional formatting. If the sheet is sorted or filtered, a status column also makes it possible to filter directly for expired or soon-to-expire records.

For many business processes, an Excel reminder alone is not a substitute for an assigned owner, a review schedule, or a notification system. If someone must act before an expiry date, decide who monitors the file and how often it is checked. Conditional formatting works best as a quick visual aid within a maintained process.

Frequently Asked Questions

Can Excel automatically highlight dates that are about to expire?
Yes. Create a formula-based conditional-formatting rule comparing the date cells with TODAY() and a chosen number of days. The color updates when Excel recalculates the workbook.

How do I highlight dates that expire today?
For date-only values, use a rule that checks whether the date equals TODAY(). Decide whether today should count as expired or as due today, then keep that convention consistent with the other rules.

Why does my rule highlight blank cells?
Add a nonblank test to the formula, such as $D2<>””, alongside the date comparison. Confirm that the reference points to the expiry-date column and that the formula row matches the first row of the selected range.

Can I highlight an entire row based on an expiry date?
Yes. Select the full table area before creating the formula rule, then lock the date column but not the row—for example, $D2 when the date is in column D and the selected area begins in row 2.

Will the formatting update by itself at midnight?
TODAY() updates when Excel recalculates; it is not a guaranteed scheduled midnight refresh. Automatic calculation is normally appropriate, but a workbook that stays open may need a recalculation before the displayed highlights change.

Does conditional formatting convert text into dates?
No. It applies a visual format based on values and formulas; it does not reliably convert text that resembles a date. Convert imported text to real Excel dates first, then verify the results.

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.