Excel Conditional Formatting Date Occurring: How To

How-to basics · Updated November 22, 2026

To highlight dates that are occurring today, tomorrow, yesterday, or within a specific period, use Excel’s built-in conditional formatting rule: select your date cells, go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring, and pick an option like “Today” or “Next month.” This rule applies a default format instantly, and you can customize the fill and font color in the same dialog.

What “A Date Occurring” Actually Does

The “A Date Occurring” rule is a quick, no-formula way to format cells whose dates fall into a preset relative period. It compares each cell to the current system date (the date on your computer, not the workbook’s last-saved date). The built-in options are:

  • Yesterday – the day before today
  • Today – the current date
  • Tomorrow – the day after today
  • Last 7 days – the 7-day window ending today (includes today)
  • Next 7 days – the 7-day window starting today (includes today)
  • Last month – any date in the previous calendar month
  • This month – any date in the current calendar month
  • Next month – any date in the following calendar month

This rule is a shortcut for a formula-based conditional format. For example, “Today” is equivalent to =A1=TODAY() (assuming A1 is the active cell). The built-in rule uses the TODAY() function internally, so it updates automatically when the date changes.

How to Apply the Rule (Step-by-Step)

Here’s the exact process in Microsoft Excel for Microsoft 365 on Windows:

  1. Select the range of cells that contain dates (e.g., A2:A100).
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
  3. In the dialog, choose the period from the first dropdown (e.g., “Today”).
  4. In the second dropdown, pick a default format (like “Light Red Fill with Dark Red Text”) or choose “Custom Format…” to set your own fill, font, or border.
  5. Click OK. The rule is applied instantly.

On a Mac, the path is the same: Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. In Excel for the web, you’ll find it under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring as well, though some advanced options may be limited.

Customizing the Format and Managing Rules

To change the format after applying the rule, select the range, go to Home > Conditional Formatting > Manage Rules, and edit the rule. You can set a custom number format, fill color, font style, or border. For example, to make “Today” stand out with a bold yellow fill and dark text, choose “Custom Format…” and set the fill to yellow and font to bold.

If you need to highlight multiple periods (e.g., today in red, tomorrow in green), apply the rule twice with different formats. Each rule is independent, and the order of rules matters: the first rule in the list takes precedence if two rules conflict. Use “Stop If True” if you want to prevent later rules from overriding.

Using Formulas for More Flexible Date Conditions

The built-in rule covers common cases, but for custom periods—like the last 14 days, next 30 days, or a specific date range—you need a formula-based rule. Here’s how:

  1. Select the range (e.g., B2:B50).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. Enter a formula that returns TRUE for the dates you want to highlight.
  5. Click Format to set the style, then OK.

For example, to highlight dates within the next 30 days (including today), with dates in B2:B50, select B2:B50 and use:

=AND(B2>=TODAY(), B2<=TODAY()+30)

This formula assumes B2 is the active cell (the first cell in the selected range). The rule will apply to each cell relative to its own row. For the last 14 days, use:

=AND(B2>=TODAY()-14, B2<=TODAY())

To highlight weekends (Saturday or Sunday) among dates, use:

=OR(WEEKDAY(B2)=1, WEEKDAY(B2)=7)

Note: WEEKDAY returns 1 for Sunday and 7 for Saturday by default. If your week starts on Monday, use WEEKDAY(B2,2) where 6=Saturday and 7=Sunday.

Common Problems and How to Fix Them

Several issues frequently trip up users:

  • Dates are text, not real dates. If your cells contain text like “2025-03-15” that Excel doesn’t recognize as a date, conditional formatting won’t work. Fix by selecting the cells, going to Data > Text to Columns, and choosing “Date” with the correct format. Alternatively, use =DATEVALUE(A1) in a helper column.
  • Rule doesn’t update. The built-in “A Date Occurring” rule uses TODAY(), which recalculates when the workbook recalculates. If your workbook is set to manual calculation, press F9 (Windows) or Cmd+= (Mac) to refresh.
  • Wrong range selected. If you select an entire column (e.g., A:A), the rule applies to every cell, including empty ones. Empty cells are not formatted because they don’t meet the date condition, but it’s better to limit the range to actual data.
  • Rule order conflict. If you have multiple rules, the first rule in the Manage Rules dialog wins. Reorder rules by selecting one and using the up/down arrows.
  • Dates appear as numbers. If your cells show numbers like 45234, they are formatted as numbers, not dates. Apply a date format via Home > Number Format > Short Date.

Comparing Built-in Rule vs. Formula Rule

Choosing between the built-in “A Date Occurring” and a formula rule depends on your needs:

  • Built-in rule: Fast, no formula knowledge needed, and covers the most common periods. However, it’s limited to the preset options; you cannot highlight “next 14 days” or “last quarter” without a formula.
  • Formula rule: Fully flexible—you can use any combination of functions like TODAY(), EDATE(), EOMONTH(), or DATE(). It also allows you to reference other cells (e.g., a start date in C1). The downside is that it requires writing a correct formula and understanding relative references.

For example, to highlight dates that are in the current quarter, use a formula rule with:

=AND(B2>=DATE(YEAR(TODAY()),CHOOSE(MONTH(TODAY()),1,1,1,4,4,4,7,7,7,10,10,10),1), B2<=EOMONTH(DATE(YEAR(TODAY()),CHOOSE(MONTH(TODAY()),3,3,3,6,6,6,9,9,9,12,12,12),1),0))

This is complex, but you can simplify by using a helper cell with the quarter start date. For instance, if C1 contains the first day of the current quarter, use:

=AND(B2>=C1, B2<=EOMONTH(C1,2))

Frequently Asked Questions

Why is my “A Date Occurring” rule not highlighting anything?

Most likely, your cells contain text that looks like dates, or the range is too large. Check that the cells are true dates by selecting one and looking at the formula bar—if it shows a number like 45000, it’s a date. If it shows text, convert using Text to Columns. Also ensure the rule’s range matches your data (e.g., A2:A100 not A:A).

Can I highlight dates that are overdue (before today) in red?

Yes. Use a formula rule with =A2<TODAY() (assuming A2 is the active cell). This will highlight any date earlier than today. To exclude blank cells, add =AND(A2<>"", A2<TODAY()).

Does the rule work with dates in a table (Excel Table)?

Yes, but be careful with structured references. When you apply a formula rule to a table column, use the first data cell as the active cell (e.g., =AND([@Date]>=TODAY(), [@Date]<=TODAY()+7)). If you use a regular range like A2, it will still work, but it may not adjust if you add rows. For tables, it’s safer to use structured references.

How do I highlight a whole row based on a date in one column?

Select the entire range of rows (e.g., A2:F100) and use a formula rule with a mixed reference: =$A2>=TODAY(). The dollar sign locks the column, so the rule checks column A for each row, and the format applies to all cells in that row.

Will the rule update automatically when the date changes?

Yes, as long as the workbook recalculates. The built-in rule and formulas using TODAY() are volatile, meaning they recalculate every time Excel recalculates (e.g., when you open the file or change any cell). If you want to freeze the “today” date, you would need to use a static date in a cell and reference that instead.

Can I use a date occurring rule in Google Sheets?

Google Sheets has a similar feature under Format > Conditional formatting, but it doesn’t have a one-click “A Date Occurring” preset. You need to use a custom formula like =A2=TODAY() in the “Custom formula is” option. The logic is the same, but the interface differs from Excel.

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.