Using a different cells date for conditional formatting

[peanuthater]

I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.

 

[GorD]

Should just be a matter of locking the appropriate ranges within the conditional formatting formulas ( adding appropriate $ signs)

What formulas do you have?

 

[peanuthater]

I have these in column D
"Cell Value between =TODAY() and=TODAY()-(365*2) Green"
"Cell Value between =TODAY()-(365*2) and =TODAY()-(365*4) Yellow"
"Cell Value between =TODAY()-(365*4) and=TODAY()-(365*5) orange"
"Cell Value less than or equal to =TODAY()-(365*5) red"
Every time I try to specify the column it tells me to use a valid formula so obviously im doing it wrong.

 

[TheCman81]

Set up 3 Condition format using a formula and use the following formulas:

2 years or less - =TODAY()-$D2<=730.5 - Green
2-4 years - =AND(TODAY()-$D2>730.5,TODAY()-$D2<1461) - Yellow
4 years + - =TODAY()-$D2>1461 - Red

 

[peanuthater]

It just changed everything to red except two of them are yellow. What doesnt make sense is that four of the dates are exactly the same and three are red and one is yellow. I was thinking that using DATEDIF would work?

 

[peanuthater]

I mostly got it to work. The red isnt working. If it is 5 years or more then it shows the color that I put in manually so if the cell was originally yellow and I change the date to be more than 5 years then it shows yellow. Here is what i used.
=DATEDIF(D6,TODAY(),"y")<2 -Green
=AND(DATEDIF(D6,TODAY(),"y")>=2,DATEDIF(D6,TODAY(),"y")<4) -Yellow
=AND(DATEDIF(D6,TODAY(),"y")>=4,DATEDIF(D6,TODAY(),"y")<5) -Orange
=AND(DATEDIF(D6,TODAY(),"y")>=365*5,DATEDIF(D6,TODAY(),"y")>365*365) -Red

I also tried this for the Red since its the one not working
=DATEDIF(D6,TODAY(),"y")>(365*5)

 

[GorD]

Why 365*5 for the red criteria?

Should just be 5, should it not

 

[peanuthater]

WOW I AM SO STUPID! no wonder it didnt work I used years and then switched to days...Sorry for wasting anyone's time on that one