Excel Conditional Formatting Between Dates: How to Do It

How-to basics · Updated November 19, 2026

To highlight dates between two values in Excel, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules > Between, then enter the start and end dates. For a range that should update when the dates in worksheet cells change, create a formula-based conditional formatting rule instead.

What “between dates” means in Excel

A date range usually has a start date and an end date. In Excel, “between” normally includes both endpoints: a date on the start date or end date qualifies. This is useful for marking appointments in a reporting period, orders within a campaign, or tasks due during a selected week.

Excel stores genuine dates as numbers, with a whole-number part representing the day and a fractional part representing time. Conditional formatting evaluates those underlying values; it does not understand a date that is only text in the same way. A cell might look like a date while actually containing text, especially after data is imported or copied from another system. This distinction explains many rules that seem not to work.

The built-in Between rule is convenient for a fixed interval. Formula rules are more flexible: they can refer to worksheet cells for the bounds, exclude endpoints, handle times, or apply a date test to an entire row. Choose the simplest rule that matches the task rather than adding formula complexity unnecessarily.

Use the built-in Between rule

For a quick highlight that uses fixed dates, work in Excel for Microsoft 365 on Windows:

  1. Select the cells containing the dates, such as A2:A100. Select only the date cells if you want only those cells colored; select a larger range if you intend to format other cells too.
  2. Choose Home > Conditional Formatting > Highlight Cells Rules > Between.
  3. Enter the start and end values in the two boxes. You can type dates in a format Excel recognizes, such as 1/1/2026, or choose a formatting option from the list.
  4. Choose a preset format, or select Custom Format to set the fill, font, or border, then select OK.

Use actual dates, not date-looking text. If you want the interval to change regularly, a fixed date typed into the dialog is not ideal; edit the rule each time or use the formula method below. The rule checks the values in the selected range, so selecting the wrong range is a common reason the result appears incomplete.

On Excel for Mac, the Conditional Formatting controls are on the Home tab as well, though dialog appearance may vary by release. Excel for the web also offers conditional formatting from Home > Conditional Formatting, but some rule-editing options and dialogs can differ from the Windows desktop app.

Create a rule that reads dates from cells

Put the start date in E1 and the end date in F1, then select A2:A100. Choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, enter a formula, choose Format, and confirm. With dates in A2:A100 and the boundary dates in E1 and F1, use:

=AND(ISNUMBER(A2),A2>=$E$1,A2<=$F$1)

The rule highlights a row in the selected date range when the date is on or after E1 and on or before F1. ISNUMBER prevents blank cells and text values from being treated as dates by the comparisons. The dollar signs lock the two boundary references, while A2 stays relative so Excel checks each row in turn. The formula must be written for the top-left cell of the range to which the rule applies.

After creating the rule, choose Home > Conditional Formatting > Manage Rules to check its Applies to range and edit its formula. A common mistake is using $A$2 instead of A2: that locks the test to one date, so every cell may receive the same result. Conversely, leaving E1 and F1 unlocked can cause the boundary references to shift as Excel evaluates cells across a range.

Choose inclusive or exclusive boundaries

The built-in Between rule and the formula above include both boundary dates. Sometimes the requirement is strictly between the dates, meaning the start and end dates themselves should not be highlighted. With dates in A2:A100 and boundaries in E1 and F1, this formula excludes both endpoints:

=AND(ISNUMBER(A2),A2>$E$1,A2<$F$1)

You can also exclude just one boundary. For example, to include the start date but exclude the end date, keep the greater-than-or-equal comparison for the start and use a less-than comparison for the end. Decide how endpoints should behave before building the rule; words such as “through,” “before,” and “after” can imply different comparisons in a reporting process.

Time values need particular care. A date entered without a time represents midnight at the start of that day. If A2 contains 3/31/2026 3:00 PM but F1 contains 3/31/2026 at midnight, a simple comparison to F1 will not include the afternoon entry. With dates and possible times in A2:A100, and date-only start and end values in E1 and F1, use this formula to include all times on the end date:

=AND(ISNUMBER(A2),A2>=$E$1,A2<$F$1+1)

The less-than comparison against the day after F1 includes every time on the end date without relying on a particular last time, such as 11:59 PM. Use this approach only when F1 is intended to be a date-only endpoint; if the endpoint itself includes a meaningful time, compare against that exact value instead.

Useful date-range examples

Formula-based rules can express common calendar conditions as well as manually selected periods. In each case, select the range first and make the formula’s relative cell reference match its top-left cell.

  • Highlight the current month: With dates in A2:A100, select that range and use the formula below. It evaluates the first day of the current month through, but not including, the first day of the next month. TODAY updates as the date changes, and EOMONTH returns a month-end date.

For dates in A2:A100, use:

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

  • Highlight overdue items except completed ones: Suppose due dates are in A2:A100 and status labels are in B2:B100. Select the rows or cells you want formatted, beginning with row 2, and use a rule that checks both the due date and status. For a whole-row format, set Applies to to the desired table columns, such as A2:F100.

With due dates in column A and statuses in column B, use:

=AND(ISNUMBER($A2),$A2<TODAY(),$B2<>"Complete")

The dollar signs keep the date and status columns fixed while the row number adjusts. TODAY returns the current date, so a due date earlier than today is overdue; the formula does not mark an item due today as overdue. To include today, change the less-than test to less than or equal to.

These examples assume the workbook’s dates are real Excel date values. If your data has timestamps, consider whether a “day” means midnight-to-midnight or a precise time interval before selecting comparisons.

Rule order, range selection, and formatting

Conditional formatting rules can overlap. A date may meet both a “current month” rule and an “overdue” rule, for example. Open Home > Conditional Formatting > Manage Rules and inspect the rule order and Applies to ranges. Excel evaluates rules in order; Stop If True can prevent lower rules from applying when a higher rule matches. Use it only when that behavior is intended, since it can hide another format that would otherwise appear.

Formatting choices matter too. A colored fill is easy to scan, but using a font color and fill together can reduce readability, particularly when the sheet is printed or viewed with different display settings. Consider a legend or a clear column heading if color carries meaning for other people using the workbook. Conditional formatting changes appearance, not the underlying date or the value used in calculations.

Common problems and how to fix them

  • No cells highlight: Confirm that the start date is not later than the end date, that the rule applies to the intended cells, and that the dates are numeric Excel dates rather than text. Test an affected date in a spare cell with an appropriate date format if its type is uncertain.
  • Only the first row behaves correctly: Check the formula’s references and its top-left starting cell. A formula written for A2 should usually refer to A2 when the Applies to range begins at A2, not A1 or A3.
  • The last day is missing: Check for time values. A date-only end boundary is midnight, so use the next-day exclusive comparison when all times on the final date should count.
  • Unexpected cells are colored: Inspect Manage Rules for overlapping rules, unexpected Applies to ranges, or a formula whose row or column references are locked incorrectly.
  • Dates look different on another computer: Display formats and regional date conventions can vary. Using real date values and cell references for bounds is more reliable than relying on ambiguous typed text such as 4/5/2026, which can be read differently in different locales.

If dates were imported, converting text to real dates may be necessary before formatting will behave consistently. Check a few representative cells and the source data before changing an entire column; conversion methods depend on how the imported text is structured.

When to use another approach

Conditional formatting is a visual cue, not a filter and not a data-cleaning tool. If you need to display only rows within a date interval, use the column’s filter controls or a table filter. If you need a permanent label such as “In period” for a report or downstream calculation, add a helper column with a logical test and fill it down. If the date range is used repeatedly, keep its start and end in clearly labeled cells and reference them in the rule rather than editing fixed dates in the dialog.

For a small one-time selection, the built-in Between rule is faster and easier to maintain. For changing limits, time-aware comparisons, whole-row formatting, or more specific conditions, a formula rule provides control. Whichever method you choose, document what the boundaries mean, especially whether the final date includes all times on that day.

Frequently Asked Questions

Does Excel include the start and end dates in Between?
Yes. The built-in Between rule treats the limits as inclusive. A formula using greater-than-or-equal to the start and less-than-or-equal to the end does the same.

Can the date limits come from cells?
Yes. Use a formula-based rule and refer to cells holding the limits, such as E1 and F1. Absolute references like $E$1 and $F$1 keep those bounds fixed as the rule checks other rows.

Why does conditional formatting ignore some dates?
The cells may contain text instead of real dates, the rule may apply to the wrong range, or the values may include times beyond a date-only end boundary. Check all three before rebuilding the rule.

Can I highlight an entire row based on a date?
Yes. Apply the rule to the full row range and use a formula with the date column fixed and the row relative, such as $A2 for a range beginning on row 2. Add other conditions, such as a status, in the same formula if needed.

Will a rule using TODAY update automatically?
TODAY returns the current date when Excel recalculates the workbook, so rules based on it can change over time. If calculation is set to Manual, the displayed result may not update until recalculation; check Formulas > Calculation Options if it appears stale.

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.