30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Highlight Dates Between Two Dates in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

How to Highlight Dates Between Two Dates in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To highlight dates that fall between two dates, use conditional formatting with an AND formula. Select your date column (say A2:A20), go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format, and enter =AND(A2>=DATE(2026,1,17),A2<=DATE(2026,3,19)), then pick a fill colour.

Two points that decide whether this works. The reference must be the first cell of your selection and relative (A2, not $A$2) — Excel applies the rule to every cell by shifting that reference down, so locking it highlights all rows or none.

And it’s better to put the two limits in cells and point at them: =AND($A2>=$E$1,$A2<=$E$2) lets you change the range by editing E1 and E2 rather than reopening the rule. Use $A2 with the column locked if you want the whole row highlighted rather than just the date cell.

You have dates in the column and want to highlight all the dates within the range of two dates.

Let’s say you have a start date of 17-Jan-2017 and an end date of 19-Mar-2017, and from the dates column, you want to highlight all the cells in which the date is between these two dates.

For this, the easiest way is to use conditional formatting. You can use a formula based on AND and DATE functions. You just need to create a new rule in conditional formatting with this formula. Let’s follow these steps.

Steps to Use CF to Highlight Dates Between Two Dates

  • First, select the range or column (A2:A20) where you have dates.
  • Now, go to the Home tab ⇢ Styles ⇢ Conditional Formatting ⇢ New Rule.
click on formula rule to highlight dates between two dates
  • From here, in the New Formatting Rule window, select “Use a formula to determine which cells to format”.
  • After that, in the formula input bar, enter the formula below this list.
  • Finally, click the Format button and select a color to apply to the cells.
=AND(A2>=DATE(2017,1,17),A2<=DATE(2017,3,19))

Make sure the formula refers to the first cell of the range you selected (A2 here), without dollar signs, so the rule moves down with each cell.

add formula to highlight dates between two dates.png
  • In the end, click OK.

Once you hit OK, all the cells with dates within the range you mentioned get formatted with the color you have specified. The start and end dates are included.

highlight dates between two dates in a column

How This CF Formula Works

The AND function can take more than one condition and returns TRUE if all the conditions are TRUE; otherwise, it returns FALSE. Here, the first condition checks if the date is on or after the start date, and the second checks if it’s on or before the end date.

So when you use this formula in conditional formatting, it checks the date in each cell and returns TRUE if that date is within the date range. For every cell where the formula returns TRUE, conditional formatting applies the color.

Use Start and End Dates from Cells

With DATE inside the formula, you need to edit the rule every time the dates change. Instead, enter the start date in cell D1 and the end date in E1, and use them in the rule with dollar signs, so they don’t move:

=AND($A2>=$D$1,$A2<=$E$1)

Now, when you change D1 or E1, the highlighted cells update right away.

Highlight the Entire Row

To highlight the whole row of the data instead of only the date cell, select the full data range (like A2:F20) before you create the rule, and use the formula with a dollar sign before the column letter ($A2). The column stays fixed on the dates, while the row changes for each row of data.

Frequently Asked Questions

Why are some dates not highlighted?

They are probably stored as text, not as real dates, so the comparison doesn’t work. Check them with ISNUMBER (a real date returns TRUE) and convert them into dates with Text to Columns or DATEVALUE.

Can I use the A Date Occurring rule instead?

Only for fixed periods like yesterday, this week or last month (Highlight Cells Rules ⇢ A Date Occurring). For your own start and end dates, you need the formula rule, or the “Between” rule under Highlight Cells Rules with the two dates.

How do I exclude the start and end dates?

Use greater than and less than without the equal signs:

=AND($A2>$D$1,$A2<$E$1)
Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

5 thoughts on “How to Highlight Dates Between Two Dates in Excel (Formula)”

  1. It works if entered exactly as the example. But how could I customize this to get it working with other reference than this specific example? The cells I need to highlight are all cell in column B of sheet 1. My reference date are in cells E32 and E33 on sheet 2. I can’t figure how the formula works, probably because of a lack of Excel knowledge (I don’t know the purpose of >and <, trying to find out but I don't know the name of these symbols…)

    Reply
  2. Hi Puneet,

    I’ve tried the this method but not got the result. Could you please tell me what is the issue with it.

    Reply
  3. A perhaps slightly shorter means of restricting a conditional formula to highlight only when row numbers are even is to state:
    =and(iseven(row(a1)),type other condition(s) here)
    Similarly restrict to odd numbered rows by:
    =and(isodd(row(a1)),type other condition(s) here)
    Note, XL automatically coverts lower case text of standard terms to upper case upon pressing enter to finish

    Reply
  4. XL accepts multiple different conditional formulae acting on any same area of a spreadsheet.
    Those show listed. Importantly XL applies them in the order listed but that can be altered. Sometimes the
    priority order is hard to remember & can become disordered. Consequently for easy recognition I begin priority 1 as:
    =and(1=1,enter condition here). Similarly begin subsequent priorities with =and(2=2, =and(3=3, etc.

    Reply

Leave a Comment