How to Highlight Dates Between Two Dates

You have dates in column and you want to highlight all the dates which are between the range of two dates.

Huh?

OK, alright!

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

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.
click on formula rule to highlight dates between two dates
  • Fron here, in New Rule window, select “Use a formula to determine which cell to format”.
  • After that, in formula input bar, enter below formula.
  • Finally, select a color from formatting option to apply to cells.
add formula to highlight dates between two dates.png
  • 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 the color you have specified.

highlight dates between two dates in a column

How this CF formula works

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

With that TRUE, conditional formatting apply color to all the cells with dates.

Related Functions

  1. AND Functions

Must Read Next

  1. 8 Examples for Using Formulas in Conditional Formatting
  2. The 3 Authentic Ways To Highlight Blank Cells in Excel
  3. SUMIFS is the Best Method to SUM Values Between Two Dates in Excel
  4. 5 Custom Date Formats You Have To Learn Today Right Now
  5. How to Convert Text to Date in Excel

Content Protection by DMCA.com
2018-11-16T06:13:26+00:00

4 Comments

  1. Vivek SIngh 14 Jul, 18 at 7:27 am - Reply

    Hi Puneet,

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

    • Puneet 15 Jul, 18 at 9:10 am - Reply

      Where you stuck?

  2. Geoff Green 9 Sep, 17 at 3:53 am - Reply

    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

  3. Geoff Green 9 Sep, 17 at 3:38 am - Reply

    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.

Leave A Comment