excel conditional formatting on a date range

Question

hi, im trying to do conditional formatting on a date range if the year value of the date is between 2 years and then format the row in a different colour. ive got it to work for values greater than 5 years but how do i tinker the formula to get it to check between less than 5 years and more than 4 yours...if that makes sense... see my formula below

so formula to highlight rows red is year element of date in H6 is greater than 4:

=DATEDIF($H6,TODAY(),"y")>4

would this be the correct way to write it and highlight in amber if between 2 year values?:

=AND(DATEDIF($H6,TODAY(),"y")>4, DATEDIF($H6,TODAY(),"y")<5)

thanks for your help in advance

Answer 1

Use
=DATEDIF($H6,TODAY(),"y")=4

For two years.

=or(DATEDIF($H6,TODAY(),"y")=4,DATEDIF($H6,TODAY(),"y")=5)

Answer 2

Hi,

The condition "less than 5 years and more than 4 years.." is empty. Why? Because we are looking at years when we should be thinking days.

Let's assume that you are interested in date between 4 and 5 years in the future from today (excluding the endpoint (=4 and =5).

This means dates between 7/12/2027 and 7/10/2028. How do we calculate these two dates (relative to TODAY?

=EDATE(TODAY(),4*12)+1

=EDATE(TODAY(),5*12)-1

we can think of these as

>EDATE(TODAY(),4*12)

<EDATE(TODAY(),5*12)

You can convert 4*12 = 48

And 5*12 = 60

So the conditional format equation we want is:

=AND($H5>EDATE(TODAY(),4*12),$H5<EDATE(TODAY(),5*12))

I started on row 5. Also I referenced dates in the future, if you want dates in the past just put a minus in front of the 4*12 and 5*12 and reverse the ><:

=AND($H5<EDATE(TODAY(),-4*12),$H5>EDATE(TODAY(),-5*12))