Home ➜ Excel Formulas ➜ **How to Highlight Dates Between Two Dates**

**Table of Content**Close

You have dates in the column and you want to highlight all the dates which are 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 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.

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 of all, select the range or column (A2:A20) where you have dates.
- Now, go to Home Tab ⇢ Styles ⇢ Conditional Formatting ⇢ New Rule.

- From here, in the New Rule window, select “Use a formula to determine which cell to format”.
- After that, in the formula input bar, enter the below formula.
- Finally, select a color from the formatting option to apply to cells.

- In the end, click OK.

Once you hit OK, all the cells where dates are within the range which you mentioned will get formatted with the color you have specified.

### How this CF formula works

AND function can take more than one condition and returns TRUE if all the conditions are TRUE, else FALSE.

So when you use this formula in conditional formatting it checks the date in the cell and returns TRUE if that date is within the date range. With that TRUE, conditional formatting apply color to all the cells with dates.

## More Formulas

- Add Month to a Date
- Add Years to Date
- Add-Subtract Week from a Date
- Calculate Age using Date of Birth
- Compare Two Dates
- Convert Date to Number
- Count Years Between Two Dates
- Create a Date Range
- DATEDIF Function in Excel
- Get Day Number of Year
- Get Month from a Date
- Get Quarter from a Date [Fiscal + Calendar]
- Get Years of Service
- Get Day Name from a Date in Excel
- Get End of the Month Date in Excel
- Get Total Days in Month in Excel
- Quickly Concatenate Two Dates
- Random Date Generator (Excel Formula)
- 5 Custom Date Formats I Always Use and You Should Too
- Convert Text to Date
- Get First Day (Beginning) of the Month

Hi Puneet,

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

Where you stuck?

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

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.