How to Format Dates to Colors, That Are Over 1 Year, 2 Years, and 3+ Years

[KAB923]

I need conditional formatting that would highlight dates in my chart that are 1 year (green), 2 years(yellow), and 3 years+(Red), and missing dates(red). I want them to refer to a specific date like from Sept 1, or whatever date i choose. I have tried different things, none work. Any help would be appreciated.
Thanks!!
Kim

[Bernie Deitrick]

Enter the date of interest 9/1/2015 into a cell - let's say cell B2.

Then for the CF, select your cells with the dates and add a new rule, use the "Use a function to determine which cells to format" option, with a formula like this for the Red

=XXXX<DATE(YEAR($B$2)-3,MONTH($B$2),DAY($B$2))

where XXXX is the address of the the first of you selected date cells, like C5

=C5<DATE(YEAR($B$2)-3,MONTH($B$2),DAY($B$2))

Then add the others:

Yellow:
=C5<DATE(YEAR($B$2)-2,MONTH($B$2),DAY($B$2))
Green:
=C5<DATE(YEAR($B$2)-1,MONTH($B$2),DAY($B$2))
And Red again:
=C5=""

[KAB923]

Thanks Bernie,
But... it didn't work.

[Bernie Deitrick]

Here you go....

CF Format Dates.xlsx

[KAB923]

Bernie, I could kiss you right now! LOL
I hope i can put it in my spreadsheet, and not screw it up. I did what you told me, and I'm a quick learner, this was just hard!!! Your so smart!
Have a great weekend.

[Bernie Deitrick]

Well, it was my bad - I forgot to tell you that order is important, since a date that is 3 years or older is also 2 years or older and 1 year or older. So I also used the Stop if true option, so make sure you use that as well.

[KAB923]

Thanks, I will try it next week!!!

[KAB923]

Bernie,
I hate to bother you AGAIN! But, I did exactly how you have them, there is a peach color in there and it doesn't seem to be working, i have attached my sheet, would you mind looking at it. I was just going to forget it, but i guess it can't hurt to ask. I do not know why this is so hard. List2 Formatting.xlsx

[Jonmo1]

The dates that appear 'Peach' are NOT more than 1 year old, so none of the conditional format rules apply to those cells.
'Peach' would appear to be the Standard Formatting Fill color for those cells.

[Bernie Deitrick]

That is the base format color. Select all the cells, and either remove the fill or select another color that you prefer.

[KAB923]

That's awesome, sorry i didn't realize it was the selected background, im like what the he** haha. Thanks again Bernie!