Conditional Formatting Date Ranges

[LeighMacKay7]

Hi all,
I am hoping you can help me.

I have a number of dates displayed dd/mm/yyyy hh:mm, which I need to add some conditional formatting too the column.

What I would like, is a formula which includes =TODAY() and helps me with the following.

> Between todays date and 5 months ago = GREEN text
> Greater than 5 months ago and less than and equal to 6 months = AMBER text
> Greater than 6 months = RED text.
There is also a 4th option, which is text based and says "No Staff Information Form", which would need to be displayed in another colour text (maybe purple?)

I have messed about in Conditional Formatting and have had no joy.
New Formatting Rule > Use a formula to determine which cells to format.=DATEDIF(TODAY(),T2:T1000,”m”)<=5



I have watched a couple of videos but have been unsuccesful.

Any help/pointers would be greatly received.

 

[Joe4]

Note that the formulas he used for the Conditional Formatting are similar to ones I showed in the post before.
But also pay close attention to my comments about what you want to do if they are EXACTLY equal to 5 or 6 months.
Which formulas you add the "=" to after the ">" or "<" will determine which rule they fall under.
So that needs to be given careful thought.

 

[LeighMacKay7]

It is just like I said.

Amber:

=AND(T2>EDATE(TODAY(),-6),T2<EDATE(TODAY(),-5))

Green:

=T2<EDATE(TODAY(),-6)

Note that you will need to decide what to do if it is EXACTLY 5 months or 6 months, and change some ">" to ">=" and/or "<" to "<=" where necessary.

Thank you. I can also use these for Amber and Green.

 

[LeighMacKay7]

Note that the formulas he used for the Conditional Formatting are similar to ones I showed in the post before.
But also pay close attention to my comments about what you want to do if they are EXACTLY equal to 5 or 6 months.
Which formulas you add the "=" to after the ">" or "<" will determine which rule they fall under.
So that needs to be given careful thought.

RED Conditional formatting = Anything greater than or equal to 6 months.
=T2<EDATE(TODAY(),-6)

AMBER Conditional formatting = Anything Greater than 5 months and less than 6 months.
=AND(T2>EDATE(TODAY(),-6),T2<EDATE(TODAY(),-5))

GREEN Conditional formatting = Anything less than 5 months.
=T2<EDATE(TODAY(),-5)

 

[Joe4]

RED Conditional formatting = Anything greater than or equal to 6 months.
=T2<EDATE(TODAY(),-6)

If that is the case, then you will need to make the following update, as I mentioned in my last two posts:

=T2<=EDATE(TODAY(),-6)

Likewise, you will need to determine which rule you want EXACTLY equal to 5 months to fall under, and add an equal sign there too as well.