Using a different cells date for conditional formatting

peanuthater

New Member
Joined
Jul 31, 2012
Messages
5
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.
exceldates.png
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Should just be a matter of locking the appropriate ranges within the conditional formatting formulas ( adding appropriate $ signs)

What formulas do you have?
 

peanuthater

New Member
Joined
Jul 31, 2012
Messages
5
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

Well-known Member
Joined
Feb 28, 2012
Messages
535
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

New Member
Joined
Jul 31, 2012
Messages
5

ADVERTISEMENT

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?
 
Last edited:

peanuthater

New Member
Joined
Jul 31, 2012
Messages
5
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Why 365*5 for the red criteria?

Should just be 5, should it not
 

peanuthater

New Member
Joined
Jul 31, 2012
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,428
Messages
5,596,067
Members
414,042
Latest member
Swiftkoala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top