Conditional Formatting by Date Range in Excel

Yes. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For dates in A2:A100 and inclusive start and end dates in C1 and C2, use =AND($A2>=$C$1,$A2<$C$2+1); choose a format and select OK. The end-plus-one test also includes times later on the end date.

What date-range conditional formatting does

Conditional formatting applies visual changes when cells meet a rule. For a date range, the rule checks whether a date falls at or after a starting point and before or at an ending point. Excel then changes the cell’s appearance automatically as the value or the bounds change. You can use a fill color, font color, border, or another available format to make dates easier to scan.

Dates in Excel are stored as numbers, with a date’s displayed appearance controlled by its number format. That is why comparisons can work even when one cell displays a date like 10/15/2026 and another displays October 15, 2026: if both are genuine Excel dates, the underlying values can be compared. A date typed or imported as text may look right but fail a date comparison, so checking the underlying value is an important first step when a rule does not behave as expected.

A range can mean different things in different workbooks: a fixed project period, a month that moves forward automatically, a rolling number of days, or all dates from one start date onward. The right rule depends on whether the limits should remain fixed or change as the calendar moves.

Set up a rule for two date bounds

Select the cells containing dates, such as A2:A100. If you want to color whole records instead, select the full rows of data, such as A2:F100.
On the Home tab, choose Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter a formula that compares the date cell with the start and end cells.
Select Format, choose the appearance you want, then select OK to create the rule.

For this formula, assume dates are in A2:A100, the start date is in C1, and the end date is in C2. If A2 contains October 15, 2026, C1 contains October 1, 2026, and C2 contains October 31, 2026, the formula returns TRUE for A2, so Excel applies the chosen format. The dollar signs keep the bound references fixed as the rule evaluates later rows; the row number in A2 is deliberately not fixed.

The formula uses a less-than comparison against the day after the end date. That detail matters when the date cells contain times as well as dates. If all values are date-only values, an inclusive comparison such as less than or equal to C2 also works. The end-plus-one version is generally safer because a timestamp on October 31 at 3:00 p.m. is later than midnight at the start of October 31.

Choose the right formula for your date values

For date-only cells, an inclusive range can be written with greater-than-or-equal and less-than-or-equal comparisons. For date-and-time values, use a strict less-than test against the day after the end date, as in the opening example. Both tests include the start date; the difference is how they handle times on the final date.

Assume the dates are in A2:A100 and the two bounds are in C1 and C2. If A2 is October 31, 2026 at 3:00 p.m., C1 is October 1, and C2 is October 31, this formula returns TRUE and includes that timestamp:

=AND($A2>=$C$1,$A2<$C$2+1)

To apply a rule to an entire record based on a date in just one column, lock the date column but leave the row relative. Assume each record occupies A2:F100, its date is in column D, and the bounds are in H1 and H2. If D2 is within those bounds, this formula returns TRUE for row 2 and formats the selected cells across that row:

=AND($D2>=$H$1,$D2<$H$2+1)

In the rule dialog, the formula is interpreted relative to the top-left cell in the selected range. If the selected range begins at A2, a reference such as $D2 checks column D on the corresponding row. Accidentally locking the row as $D$2 would make every record check the same date cell, a common cause of apparently inconsistent highlighting.

Use fixed dates, rolling windows, or a changing month

Cell references are usually easier to maintain than dates typed into a formula. Put the boundaries in worksheet cells so someone can change the period without editing the rule. For a one-time report with permanent dates, a fixed formula is possible, but a start and end cell make the rule clearer and more reusable.

For a rule that always highlights dates in the current calendar month, assume dates are in A2:A100 and the workbook uses the system date shown by TODAY. On November 2, 2026, the formula below evaluates the period from November 1 through November 30. A date of November 15 returns TRUE; a date of December 1 returns FALSE:

=AND($A2>=EOMONTH(TODAY(),-1)+1,$A2<=EOMONTH(TODAY(),0))

For a rolling period, decide whether the current day belongs in the window and whether the range measures calendar dates or elapsed hours. For example, assuming dates in A2:A100, this rule checks today and the following six calendar dates, a seven-date window. On November 2, 2026, November 2 returns TRUE and November 9 returns FALSE:

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

Because TODAY updates with the system date when Excel recalculates, a rolling or current-month rule changes over time without manual edits. That can be useful for schedules and dashboards, but it may be unsuitable for an archived report that needs to preserve the view as it was on a particular day. In that case, use fixed boundary cells or save a dated copy of the report.

Highlight whole rows and manage overlapping rules

To make a schedule readable, it is often more useful to highlight an entire row than just the date. Select the full data range first, and write the rule so it refers to the date column in each row. Use the dollar sign before the column letter to keep the check in that column as Excel applies the rule across other selected columns. Do not add a dollar sign before the row number unless every row should refer to exactly the same date.

More than one conditional formatting rule can apply to the same cells. For example, one rule might mark a date range in blue while another marks overdue work in red. If rules assign conflicting formats, their order and the Stop If True setting can affect what appears. Open Home > Conditional Formatting > Manage Rules to review the rules, their order, and the ranges they apply to. Keep rules as specific as practical, and avoid creating several nearly identical rules for overlapping selections.

Be careful when copying cells that already have conditional formatting: copying can also copy or extend the rule, sometimes creating extra entries or changing the Applies to range. After editing a formatted table, check Manage Rules if highlights appear in unexpected places. For workbooks shared with other people, label the boundary cells clearly so readers know what period the formatting represents.

Common problems and how to fix them

The date looks correct but is not highlighted. The value may be text rather than an Excel date. Re-enter or convert the value to a date, then confirm that the start and end cells also contain real dates.
The first row works but later rows do not. Check that the date reference has a relative row, such as $A2, and that the formula’s row number matches the first row in the Applies to range.
The final day is missing. If date cells include times, a comparison with less than or equal to an end date at midnight excludes later times on that day. Compare with less than the end date plus one instead.
Blank cells are formatted unexpectedly. Add a nonblank test to the rule if empty cells should never match. For dates in A2:A100 and bounds in C1:C2, the rule can require A2 to contain a value as well as meet the date limits:

For this blank-safe rule, assume dates are in A2:A100 and bounds are in C1:C2. If A2 is empty, the formula returns FALSE and does not apply the format; an in-range date returns TRUE:

=AND($A2<>"",$A2>=$C$1,$A2<$C$2+1)

Another frequent issue is a reversed or incomplete boundary: the end date is earlier than the start date, or one bound cell is blank. Check both cells and their displayed formats before changing the rule. Also verify that the selected range in Manage Rules is the intended range; a correct formula applied to the wrong cells will still produce a confusing result.

Formula rules versus built-in date options

For a quick rule that marks a date as today, yesterday, or another available relative date, Excel’s built-in date options can be faster than writing a formula. Choose Home > Conditional Formatting and review the date-related choices in the menu. The exact available presets are less flexible than a formula when a reader needs two custom bounds, a whole-row highlight, a time-aware end date, or a range stored in worksheet cells.

The formula approach is useful when the date limits are user-controlled or the rule needs to respond to other columns. A built-in preset is convenient for a simple, familiar condition. In either case, conditional formatting changes only the appearance; it does not filter, sort, change, or validate the underlying dates. Use Excel’s filter controls or a separate calculation when you need to act on the matching records rather than just draw attention to them.

In Excel for Microsoft 365 on Mac, conditional formatting and formula-based rules are also available, though ribbon layout and dialog details can differ from Windows. Excel for the web supports conditional formatting, but some advanced editing options may be less convenient than in the desktop app. If you cannot find a rule-management option in the web interface, open the workbook in desktop Excel to inspect or edit the full rule setup.

Frequently Asked Questions

Can conditional formatting highlight dates between two cells?
Yes. Put the start and end dates in separate cells and compare each date against both cells in a formula rule. Lock the boundary references with dollar signs so they remain fixed as the rule applies to other rows.

Can I highlight a date range across an entire row?
Yes. Select the full table range, then write the formula to check the date column for each row. Lock the date column, but leave the row number relative, so each row is tested against its own date.

Why does the last date in my range not highlight?
A date cell may include a time, even if the display shows only the date. A test that ends at midnight can exclude later times on that day. Use a less-than comparison against the day after the end date to include all times on the final date.

Will a date-range rule update automatically?
It updates when a cell value changes. Rules based on TODAY also move with the current system date when Excel recalculates. Rules using fixed boundary dates do not move unless you edit those dates or use formulas that calculate new bounds.

Does conditional formatting change the date value?
No. It changes how a cell looks, not the stored value. The cell remains available for calculations, sorting, and filtering; conditional formatting is a visual cue rather than a data-cleaning or filtering operation.

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.