Excel Conditional Formatting for Upcoming Dates

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+7) for dates in A2:A100. Choose a format and click OK. This highlights dates from today through seven days from today, inclusive, provided the cells contain real Excel dates.

What “upcoming” means in a date rule

Conditional formatting changes a cell’s appearance when a condition is true; it does not alter the cell’s value. For upcoming dates, the condition typically compares each date with today’s date and a chosen future limit. The example above includes today and the next seven calendar days. If you want to flag only future dates, excluding anything due today, adjust the lower boundary.

There is no single definition of “upcoming” that fits every schedule. A bill might need a three-day warning, a project milestone could use two weeks, and a renewal list might look a month ahead. Decide the period first, then use that number of days in the rule. The formula updates as the current date changes, unlike a rule based on a date typed in once.

Excel stores valid dates as serial numbers, so comparisons such as “greater than or equal to today” work on date values. The ISNUMBER check in the example prevents blank cells and text entries from being treated as qualifying dates. It is useful for lists that are incomplete or contain notes alongside dates.

Set up the rule step by step

For a simple list with dates in column A and a header in A1, apply the rule to A2:A100. The first cell in the range matters: the formula refers to A2 because that is the top-left cell being evaluated. Excel adjusts the relative reference for each row, checking A3 on the next row, then A4, and so on.

  1. Select the cells to format, such as A2:A100. Avoid including the header unless you want Excel to evaluate it too.
  2. On the Home tab, choose Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the formula, then choose Format to set a fill, font color, border, or other available formatting.
  5. Click OK to close the formatting dialog, then click OK again to create the rule.

For the selected range A2:A100, the formula is =AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+7). In the Conditional Formatting Rules Manager, check that the Applies to range is the range you intended. If your dates begin in a different cell, change A2 in the formula to the first cell of that range. Keep the column and row reference relative unless you have a specific reason to lock one with dollar signs.

Excel for Mac has conditional formatting tools on the Home tab as well; the New Rule dialog may look somewhat different depending on the release, but the formula-based rule works the same way. Excel for the web also supports conditional formatting, though its dialogs and options are not identical to the desktop app. If you cannot find the same formula-rule controls in your version, create or edit the rule in desktop Excel.

Choose the right date window

Change the number added to TODAY() to set a different window. With date-only values in A2:A100, a 30-day look-ahead uses =AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+30). It includes today and the following 30 days. For a future-only window of seven days, excluding today, use =AND(ISNUMBER(A2),A2>TODAY(),A2<=TODAY()+7). Each formula assumes that A2 is the first date cell in the selected range and that the cells contain date values rather than text.

When a date list also includes times, the upper boundary needs extra care. Excel stores a time as a fraction of a day, so a timestamp on the last day may be greater than TODAY()+7, which represents midnight at the start of that day. If the intention is to include all timestamps through the seventh day, use a less-than boundary for the following day.

With timestamps in A2:A100 and A2 as the first cell, this formula includes today and the next seven calendar days, through the end of the seventh day: =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+8). The strict less-than comparison excludes midnight at the start of day eight and anything later. For date-only cells, the simpler inclusive formula is easier to read.

Highlight an entire row instead of just the date

Many tracking sheets have a date in one column and a task name, owner, or status in other columns. To color a whole record according to its date, select the body of the table, such as A2:D100, and create a formula rule. Lock the date column with a dollar sign but leave the row number relative. This tells Excel to check column D for every selected row.

With due dates in D2:D100 and the rows to format in A2:D100, use =AND(ISNUMBER($D2),$D2>=TODAY(),$D2<=TODAY()+7). Choose a fill color, and each row with a qualifying due date will be highlighted. The dollar sign before D keeps the check in the due-date column as the formatting moves across the row; the missing dollar sign before 2 lets Excel evaluate the correct row.

For a rule applied only to the due-date cells in D2:D100, use D2 rather than $D2 as the reference. Absolute and relative references are a frequent source of confusion: the reference in the formula should correspond to the top-left cell of the Applies to range, with dollar signs added only for parts that should stay fixed.

Make separate rules for due today and overdue dates

It is often clearer to use different colors for dates due today, dates coming soon, and dates that have passed. Separate rules let a reader distinguish urgency at a glance. For date cells in A2:A100, a due-today rule can use =AND(ISNUMBER(A2),A2=TODAY()). An overdue rule can use =AND(ISNUMBER(A2),A2<TODAY()). Both formulas assume that A2 is the first cell of the selected range.

To distinguish upcoming dates without including today, use a future-only rule such as =AND(ISNUMBER(A2),A2>TODAY(),A2<=TODAY()+7). Assign each condition a distinct format, such as red for overdue, amber for the coming week, and a separate accent for today. Keep the colors readable and do not rely on color alone if the sheet will be printed or used by people with color-vision differences; a status column or clear labels can provide another cue.

If rules overlap, check their order in Conditional Formatting > Manage Rules. Excel applies rules according to their priority, and the Stop If True option can prevent lower-priority rules from applying when a higher-priority condition matches. For simple, mutually exclusive ranges—overdue, today, and future—the comparisons above avoid most overlap, but the manager is still worth checking when a workbook has several formatting rules.

Why a date may not highlight

The most common cause is that a value that looks like a date is actually text. This can happen after copying data from a website, importing a CSV file, or using a date format Excel does not recognize in the current regional settings. Changing the number format alone does not convert text into a date. One clue is that changing the cell’s number format does not change how the value displays, or that sorting the column produces an unexpected order.

  • Confirm the rule’s Applies to range includes the cell you expect to format.
  • Check that the formula’s cell reference matches the top-left cell of that range.
  • Look for text dates, leading apostrophes, extra spaces, or blanks in the date column.
  • Confirm that the workbook is calculating formulas automatically; TODAY() changes when Excel recalculates.
  • Review other conditional-formatting rules that may override the chosen appearance.

Regional date formats can also cause ambiguity. A string such as 03/04/2026 could be interpreted differently depending on date conventions, while a genuine Excel date is stored as a number and displayed according to the chosen format. When importing data, use a consistent, unambiguous date format or convert the imported text into actual dates before relying on date comparisons.

TODAY() returns the current date without a time component. It is a volatile function, meaning Excel recalculates it as part of workbook calculation, but a conditional format is not a real-time alarm or notification. If a workbook stays open across midnight, its display may not update at the exact moment the date changes; recalculation or reopening may be needed depending on calculation settings and activity.

Formula rules or built-in date rules?

Excel also offers built-in date-related conditional-formatting options in the Highlight Cells Rules menu, including rules for dates occurring in periods such as yesterday, today, tomorrow, and the coming week. These can be quicker when a preset matches the task. They are less adaptable when the required window is unusual, when you need a precise inclusive or exclusive boundary, or when a rule should format an entire row based on a date in another column.

A formula-based rule is more explicit and can include safeguards such as ISNUMBER. It also makes custom ranges straightforward: change the number of days in the formula, or combine the date condition with another condition such as a status. The trade-off is that references must be written correctly and the Applies to range must be managed. For a one-off, familiar preset, the built-in option may be simpler; for a reusable tracker, a formula gives finer control.

Frequently Asked Questions

How do I highlight dates due in the next 7 days in Excel?
For date-only values in A2:A100, select that range and create a formula-based conditional-formatting rule using =AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+7). It includes today and the next seven calendar days.

How do I exclude today from upcoming dates?
With the first date in A2, replace the lower comparison with A2>TODAY(). For example, the future-only seven-day condition is =AND(ISNUMBER(A2),A2>TODAY(),A2<=TODAY()+7). That leaves out dates equal to today.

Will the highlighting update every day?
Yes, because TODAY() returns the current date when Excel recalculates. It is not a scheduled notification, however, and it may not refresh at precisely midnight while a workbook is open and idle.

Can I highlight the whole row based on a date in one column?
Yes. Select the rows to format and use a formula that locks the date column but not the row number. For dates in column D and a range beginning on row 2, the reference starts with $D2.

Why are my upcoming dates not being highlighted?
Check that the dates are real Excel date values, the rule covers the correct cells, and its formula points to the first cell in the selected range. Also inspect rule priority and confirm that calculation is enabled.

Practical tips for a dependable tracker

Keep the date field consistent, label the column clearly, and choose a time window that matches the action people need to take. A short look-ahead is useful for daily follow-up; a longer one may suit renewals or planning. Consider adding a separate status such as Complete or Cancelled so a completed item is not still colored as urgent just because its date is near. If the highlight should depend on status too, the date rule can be combined with that status condition, but the row and column references must match the layout.

Finally, test the rule with a few known values: yesterday, today, the last included day, and the first excluded day. Temporary sample dates make boundary mistakes easier to catch. Once the range, date values, and rule logic are right, conditional formatting provides a live visual cue without changing the underlying schedule or requiring dates to be recolored manually.

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.