Conditional formatting based on number of years from now

[jhiltabidel]

I have a list of 2000 pieces of inventory. Only the year of purchase is associated with each one, not a specific month or day. I'm trying to apply conditional formatting so that...

Less than 2 years old = green
Between 2 and 4 years old = yellow
Between 4 and 6 years old = orange
More than 6 years old = red

I've been trying =TODAY()-365 (365 replaced by number of days in the above named ranges) but the cell only changes to the color of the most recently applied formatting rule. Any help would be appreciated.

[popipipo]

=a1<year(today())-6
=a1<year(today())-4
=a1<year(today())-2
=a1,year(today())
Try these one

[Fotis1991]

Something like this?

Please Login or Register  to view this content.

For the Green color.