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.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,445
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
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,445
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
 

Forum statistics

Threads
1,081,841
Messages
5,361,638
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top