Highlight Upcoming Dates in Excel with a Formula Rule

To highlight dates coming up in the next 30 days, select the date cells and create a formula-based conditional formatting rule. If your dates are in A2:A100, use =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+31), then choose a fill color. The rule updates as the current date changes, so you can quickly spot deadlines, appointments, renewals, or other dates that need attention.

This guide uses Microsoft Excel for Microsoft 365 on Windows. You will need a worksheet with dates stored as real Excel dates, not text that only looks like dates. The steps also explain how to change the time window, highlight an entire row, and diagnose the most common problems.

What You’ll Need

An Excel workbook with dates entered in a column. For the main example, the dates are in A2:A100 and A1 is a heading.
Permission to edit the workbook and access to the Home tab’s Conditional Formatting commands.
A decision about what “upcoming” means for your task—for example, today through the next 30 days, or the next seven days excluding today.

Step-by-Step

Check the date column. Make sure each cell in A2:A100 contains a valid Excel date. Click a few cells and look at the formula bar; a valid date should be stored as a numeric date value, even if it displays in a custom format. Keep the heading outside the selected range so Excel does not try to evaluate it as a date.
Select the cells to format. Select A2:A100, or select the actual range containing your dates. If the list is likely to grow, you can select a larger practical range, such as A2:A500. Avoid selecting the entire worksheet column unless you need to: a focused range is easier to manage and can reduce unnecessary rule calculations.
Open the formula rule option. On the ribbon, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule window, select Use a formula to determine which cells to format. This option lets you define a moving date window with a formula rather than a fixed calendar date.
Enter the date-window formula. With dates in A2:A100 and A2 as the first selected cell, enter =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+31) in the formula box. ISNUMBER makes the rule ignore blanks and text. The two comparisons include dates from today through 30 days after today, inclusive. TODAY updates automatically when Excel recalculates the workbook.
Choose the highlight style. Click Format, then use the Fill tab to select a background color. You can also set a font color or use the other available formatting choices. Choose a shade with enough contrast for the date text to remain readable, then click OK to close the Format Cells window and OK again to create the rule.
Check the Applies to range. Open Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, choose This Worksheet if needed. Verify that the new rule applies to A2:A100, or to the range you selected. If the first formula reference is A2, the range should start at row 2 so each row checks its own date.
Test the result. Find a date that falls within the window and confirm it is highlighted. A date yesterday or more than 30 days away should not be highlighted. For a temporary check, you can enter a sample date in an unused cell within the range, then undo or remove that test value. Remember that TODAY is based on the computer’s current date, not a date typed elsewhere in the workbook.
Save the workbook. Save after confirming the result. The conditional formatting rule is part of the workbook, so other people who open the file in Excel should see the highlighting too. If you share it with another spreadsheet program, check the appearance there because conditional formatting behavior can vary.

For a shorter window, change the number added to TODAY. For example, with dates in A2:A100 and A2 as the first cell in the selected range, this formula highlights today and the following six calendar days: =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+7). This is useful for a one-week look-ahead. To exclude today, change the lower comparison from greater than or equal to TODAY to greater than TODAY. The rule will then select future dates only.

You can also highlight an entire row when a date in one column is approaching. Suppose each record is in A2:D100 and its due date is in column C. Select A2:D100, create a formula rule, and enter =AND(ISNUMBER($C2),$C2>=TODAY(),$C2<TODAY()+31). The dollar sign fixes the date column at C, while the row number remains relative so each row checks its own due date. Set the Applies to range to A2:D100. Without the dollar sign, Excel may check different columns as the formatting moves across the row.

Common Mistakes

Using text instead of dates. A value such as an imported date may look correct but be stored as text. ISNUMBER excludes text, so it will not receive the highlight. Convert text dates to real dates before relying on the rule.
Starting the selection on the wrong row. Conditional formatting formulas are written relative to the top-left cell of the Applies to range. If the range starts at A2 but the formula refers to A1, each row may evaluate the wrong cell.
Including the heading in the range. Select the date values only, not the column heading. Including the heading can lead to confusing results if the rule is later edited or expanded.
Choosing a fixed-date rule by accident. A rule that contains a date typed directly into it will not move forward each day. Use TODAY in a formula rule when the highlight should track the current date.
Misunderstanding the endpoint. TODAY()+31 with a less-than comparison includes today and the next 30 calendar days. If you change the comparison or number, check whether you want to include the endpoint and whether today itself should count.
Forgetting other rules. A separate conditional formatting rule may apply a competing fill color. Check Manage Rules if the date appears to qualify but the expected color is missing.

Tips

Use a clear visual system. One color can signal the upcoming window, while a separate rule can distinguish overdue dates. Keep the palette consistent and accessible; do not rely on color alone if the sheet is used for critical work.
Make the window easy to adjust. Put the number of days in a labeled cell, such as F1, if users will change the look-ahead period. With dates in A2:A100, F1 holding the number of days, and A2 as the first cell, use =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+$F$1+1). The added 1 includes the endpoint day. Keep F1 numeric and nonnegative.
Consider a formatted Excel Table. If you convert the list to a table with Insert > Table, new rows can inherit formatting behavior more conveniently. Still verify the conditional formatting rule’s Applies to range after adding rows, especially in a workbook with several rules.
Keep the source values intact. Conditional formatting changes appearance, not the date stored in the cell. You can still sort, filter, or calculate with the dates; the color is a visual cue rather than a replacement for the underlying data.
Be aware of recalculation. TODAY is a volatile function: Excel recalculates it as the workbook recalculates. If the date does not appear to update after midnight, press F9 on Windows to recalculate. On Mac, use Fn+F9 on keyboards where the function keys control hardware features, or use Formulas > Calculate Now.

Troubleshooting

Nothing is highlighted. First confirm that a date in the selected range falls between today and the end of the window. Then open Manage Rules and verify both the formula and Applies to range. Check that the formula references the first cell in the range, such as A2 for A2:A100, and that the formula begins with an equals sign.

The dates look right but are ignored. Select a problem cell and change its number format temporarily to General. A real Excel date will display as a number; text may remain text. If the dates came from a CSV or web export, select the affected cells and try Data > Text to Columns, then follow the prompts to convert them to dates. Confirm the correct date order for your region before finishing. You can also re-enter a few values as dates to test whether the rule works.

Dates from the past are highlighted. Check that the lower comparison uses greater than or equal to TODAY for a window that includes today and future dates. If the formula uses only an upper limit, old dates can qualify. Also inspect overlapping rules in Manage Rules and their order; another rule may be applying a similar fill.

The wrong rows or columns change color. Match the first formula reference to the top-left cell of the Applies to range. For a whole-row rule driven by column C, lock the column with $C2 but leave the row number unlocked. If you apply the rule to a different starting row, update the formula’s row reference to match it.

The format works on one computer but looks different elsewhere. Confirm that the workbook was saved after the rule was created and that the recipient is opening it in a compatible version of Excel. Excel for Mac has Conditional Formatting and formula-based rules; the ribbon layout can differ slightly, but the rule logic is the same. Excel for the web supports conditional formatting, though some rule-editing controls may appear in different locations or be more limited than the Windows desktop version. When editing there, inspect the rule before assuming a desktop-only option is available.

Frequently Asked Questions

Can Excel highlight dates automatically as they get closer?
Yes. A conditional formatting rule using TODAY recalculates as the current date changes. The cell’s value does not need to be edited each day, although Excel must recalculate the workbook for the updated result to display.

How do I highlight only dates in the next seven days?
With dates in A2:A100 and A2 as the first cell in the selected range, use =AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+7). This includes today and the six following calendar days. To count only future days, change the lower comparison so today is excluded.

Can I highlight upcoming dates in a whole row?
Yes. Select the full row range, then make the formula refer to the date column with a dollar sign before its letter. For dates in column C and a selection starting in row 2, use the row-based example above and make sure Applies to covers the complete rows you want colored.

Does the rule work if the cells contain times as well as dates?
Yes, the main formula’s less-than comparison against the day after the final included date also handles times during that last day. Make sure the values are genuine Excel date-and-time numbers rather than text.

Can I use the same method in Excel for Mac or Excel for the web?
Excel for Mac includes conditional formatting and supports formula-based rules, though some ribbon labels and dialog layouts may differ. Excel for the web also supports conditional formatting, but advanced rule editing can differ from the Windows desktop interface. If a command is not visible, use the Home tab’s Conditional Formatting menu and check the available rule options.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.