Excel Conditional Formatting for Dates Coming Due

How-to basics · Updated November 19, 2026

To highlight Excel dates coming due, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and use =AND(ISNUMBER(D2),D2>=TODAY(),D2<TODAY()+8) if your dates start in D2. This marks dates from today through the next seven calendar days; change the range or formula to match your sheet.

What the due-date rule does

Conditional formatting changes a cell’s appearance when a rule is true. A date-based rule can make a task, renewal, payment, inspection, or other deadline stand out without changing the date itself. The example above checks that D2 contains a numeric Excel date, is not earlier than today, and falls before the start of the day eight days from today. That final boundary includes the full seventh day ahead, even if a cell contains a time as well as a date.

The rule is dynamic: TODAY() supplies the current date when Excel recalculates the workbook. As the calendar advances, dates move into and out of the highlighted window. The formatting is not a reminder or notification, though. It will not send an alert, and an open workbook may not visibly update at midnight unless Excel recalculates.

  • Coming due: a date from today through seven days ahead, using the example rule.
  • Overdue: a date before today.
  • Not due soon: a later date outside the chosen window.

Set up a rolling due-date rule

For a simple list with due dates in D2:D100, use the first cell in the selected range in the formula. In Excel for Microsoft 365 on Windows, select D2:D100, then go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the rule, select Format to choose a fill, font, or border, and confirm with OK. The formula assumes D2 is the top cell of the selected range:

=AND(ISNUMBER(D2),D2>=TODAY(),D2<TODAY()+8)

ISNUMBER helps prevent blank cells and text entries from being treated as deadlines. Excel stores valid dates as serial numbers, so a real date passes this test; a date-looking value stored as text may not. The relative reference D2 is intentional: as the rule evaluates each cell in the selected range, Excel adjusts the row reference to D3, D4, and so on.

To use a different window, adjust the number added to TODAY(). For dates due today through the next 30 calendar days, with dates in D2:D100, the formula is =AND(ISNUMBER(D2),D2>=TODAY(),D2<TODAY()+31). The 31 boundary includes today plus 30 days ahead. For a window that starts tomorrow and excludes today, use =AND(ISNUMBER(D2),D2>=TODAY()+1,D2<TODAY()+8). State the intended window clearly in a worksheet heading or note so coworkers know what the color means.

Highlight an entire row, not just the date

It is often easier to spot an approaching deadline when the task name and owner are highlighted with it. Suppose task names are in A, owners in B, status in C, and due dates in D, with records from row 2 through row 100. Select A2:D100, create a formula-based rule, and use this formula, which assumes the first selected row is row 2 and the date is in column D:

=AND(ISNUMBER($D2),$D2>=TODAY(),$D2<TODAY()+8)

The dollar sign fixes the date column at D while leaving the row number relative. That lets each row’s formatting depend on its own due date. Check the rule’s Applies to range in Conditional Formatting > Manage Rules; it should cover the intended rows, such as $A$2:$D$100. If the data begins on another row, the formula’s row number should match the top row of the Applies to range. A mismatch can make the first row use the wrong record’s date.

For a table that grows over time, consider converting the list to an Excel table with Insert > Table. Tables can extend formatting as new records are added, but review the conditional-formatting rule after adding rows to confirm its Applies to range includes them. A table does not fix an incorrect formula reference, so test a row near the bottom as well as the first one.

Add a separate overdue warning

Coming-due and overdue items have different meanings, so give them distinct formats—for example, amber for an upcoming date and red for a missed date. With dates in D2:D100, create a second rule for overdue dates using this formula:

=AND(ISNUMBER(D2),D2<TODAY())

This excludes blanks and text while identifying dates earlier than today. A date equal to TODAY() is due today, not overdue. The coming-due formula also includes today, so both rules should not overlap if you use the boundaries shown. If you choose different formulas that can overlap, open Home > Conditional Formatting > Manage Rules to review rule order and formatting. Where multiple rules apply, order and the Stop If True option can affect which formatting is shown.

Some sheets need more categories. You might use a warning color for dates due within a week, a stronger color for those due within 30 days, and another for overdue items. Write non-overlapping boundaries deliberately, then try sample dates at each edge: yesterday, today, seven days from today, and eight days from today. Boundary checks catch off-by-one errors before the rule is relied on.

Built-in date rules or a formula?

Excel’s built-in date rule is faster for standard periods. Select a range and choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. The dialog offers common periods such as yesterday, today, tomorrow, the last seven days, and next week, along with a format choice. The exact labels and available choices can vary by Excel version. Built-in options are handy when one of those named periods matches the requirement.

A formula rule is more flexible when you need a precise rolling interval, want to ignore text and blanks explicitly, or need to format an entire row based on a date column. It also makes the boundary visible for review. These choices are not mutually exclusive: a built-in rule can be convenient for a quick personal list, while a formula is often clearer for a shared tracker with a defined service window.

Be careful interpreting phrases such as next week. A built-in calendar-period rule may follow a named week rather than a rolling number of days from today. If the requirement is exactly today plus the following seven calendar days, the formula-based boundary is less ambiguous. For workbooks shared with other people, add a short legend explaining the color and period.

Fix dates that do not highlight

The most common cause is a date stored as text. Changing the cell’s number format to Date only changes how a numeric value is displayed; it does not convert text into a true date. Check a suspicious value by changing its format to General or testing it in a spare cell. If it remains text, use a reliable conversion method appropriate to the data, such as Data > Text to Columns for consistently structured date text, and verify the converted result before replacing the original values.

  • Check the rule range: In Manage Rules, confirm the rule applies to the cells you expect.
  • Check the reference: The formula should refer to the top-left cell of the Applies to range; use dollar signs only where a row or column should stay fixed.
  • Check for another rule: A different rule may apply a competing fill or font. Review its order and formatting.
  • Check the date value: A value that looks like a date may be text, or may include an unexpected time.

Time values matter at the edge of a date range. Excel stores a time as a fraction of a day. A comparison such as D2<=TODAY()+7 includes midnight at the start of the seventh day ahead but not a timestamp later that day. The recommended D2<TODAY()+8 boundary includes the entire seventh calendar day. If your business definition is a precise number of hours rather than whole calendar days, use a date-time boundary that reflects that policy instead.

Recalculation, versions, and practical maintenance

TODAY() is a volatile function: it can update when Excel recalculates. Excel normally uses automatic calculation, but a workbook set to manual calculation may retain a stale result until recalculated. Check Formulas > Calculation Options and choose Automatic if that is appropriate for the workbook. On Windows, F9 recalculates formulas; on some laptops you may need the Fn key. A recalculation can refresh the date-based display, but conditional formatting is not a dependable scheduled alert.

In Excel for Mac, the same general Home > Conditional Formatting controls and formula logic apply, although the dialog layout can differ. Excel for the web also supports conditional formatting and formula-based rules, but some rule-management details may be presented differently from the Windows desktop app. If a workbook is shared across platforms, test the rule in the version your colleagues use, especially after changing the Applies to range or adding rules.

For maintainability, keep dates in a single dedicated column, use true date values, label the date window, and avoid making a color the only indicator of urgency. A status column such as Open, Complete, or Cancelled can help distinguish completed work from genuinely actionable deadlines. If completed items should not be highlighted, include the status condition in the formula; for example, with status in C and due date in D, the formula for rows starting at row 2 is =AND($C2="Open",ISNUMBER($D2),$D2>=TODAY(),$D2<TODAY()+8). This assumes the status text is exactly Open; adjust it to match the entries in your sheet.

Frequently Asked Questions

How do I highlight dates due in the next 7 days in Excel?

For dates in D2:D100, create a formula-based conditional-formatting rule with =AND(ISNUMBER(D2),D2>=TODAY(),D2<TODAY()+8). It includes today and the next seven calendar days. If you mean seven days ahead but not today, use a start boundary of TODAY()+1 instead.

Can Excel highlight overdue dates automatically?

Yes. Apply a rule such as =AND(ISNUMBER(D2),D2<TODAY()) to the date range. It marks dates before today and leaves today’s date out. The display updates when the workbook recalculates.

Why is a blank cell highlighted as a coming date?

A blank or a text value may interact unexpectedly with a loosely written comparison rule. Add ISNUMBER to the AND condition, as in the examples above, and confirm that the rule refers to the correct cell and range. Also check whether another rule is responsible for the color.

Can I highlight a whole row based on its due date?

Yes. Select the full table range and use a formula with the date column fixed, such as =AND(ISNUMBER($D2),$D2>=TODAY(),$D2<TODAY()+8) for rows beginning at row 2. Set Applies to to the full row range you want formatted.

Will conditional formatting send me an alert when a date is due?

No. It changes cell formatting when Excel evaluates the rule; it does not send a message or guarantee an alert at a particular time. For notifications, use a separate reminder or workflow tool and treat the spreadsheet formatting as a visual status cue.

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.