Solved Color change formula to reflect time past entered date

March 30, 2013 at 02:14:48
Specs: Microsoft Excel 2010
Hey you got a few seconds can you help me with an excel formula? What I am trying to do seems like it should be an easy thing but for whatever I can’t zone in on why it’s not working. What I’m trying to do is have a cell with a date and have that cell change colors from green meaning recently done, yellow would be like 6 months from whatever date is put in the cell, and then red when that date is over a year old.

E.g.;
3/29/2013 9/29/2013 3/29/2014
But all these colors would be in one cell. I have tried a formula that I found in the help thing within excel it’s something like this =IF(TODAY()=EDATE(E4),TRUE,FALSE) format in green color. I’ve done this in the conditional formatting option along with these formulas as well; =IF(TODAY()>EDATE(E4,6),TRUE,FALSE) format yellow, and =IF(TODAY()>=EDATE(E4,12),TRUE,FALSE) format in red. For some reason I can’t get it to reflect the change when the date is changed. I know I probably sound stupid because it’s probably something super simple but I can’t stare at it anymore.


See More: Color change formula to reflect time past entered date

Report •

#1
March 30, 2013 at 09:19:53
First, this formula will never work because it is missing the months argument.

=IF(TODAY()=EDATE(E4),TRUE,FALSE)

I'm not sure what you mean by "recently done" (it's not very specific) but that formula won't work for any situation. One way to test formulas that you want to use for Conditional Formatting is to put them in a cell to see if they return TRUE or FALSE when you want them to. In this case, you would see that Excel would return an error because you are missing an argument.

Second, what you are asking for and what your formulas are checking seem to be the exact opposite of each other.

Based on the Date examples you've given, your formulas are checking to see if TODAY() is greater than a date in the future. That's never going to be TRUE.

Perhaps you meant use something like this, where the months argument is a negative number, specifying a date that has past.

=IF(TODAY()>EDATE(E4,-6),TRUE,FALSE)

Finally, this is just a tip to keep in mind when writing rules for Conditional Formatting. You don't have to use a complete IF statement with TRUE and FALSE as your return values. All you need is a formula that will return TRUE or FALSE, such as:

=TODAY()>EDATE(E4,-6)

Whether used in a cell or as CF Rule, Excel knows to return TRUE or FALSE when it evaluates that type of formula.

=A1=B1
=A1<>B1
=VLOOKUP(A2,$B$1:$D$6,3,0)="Done"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 30, 2013 at 19:44:11
Maybe I'm not clear as to what I'm trying to accomplish. I am tracking certifaction on a scale that any date in the cell 6 months or older from todays date will change to let say the color yellow and any date in the cell that is a year old would highlight red indicating that that person needs a recertifaction. So I don't really need to have a green indicator like I stated before but the other to are key. I really appericate any help with working out the formula. my other question is once I have the correct formula how do I go about making it something that is across the entire spreadsheet?
I have numbersous certifaction I have to track and I think with this it'll help me keep track...Thanks again for all your help

Report •

#3
March 31, 2013 at 11:36:50
✔ Best Answer
For Red, a date in E4 at least 12 months prior to the current date, use:

=IF(E4<>"",TODAY()>EDATE(E4,12))

For Yellow, a date in E4 between 6 months and 12 months prior to the current date, use:

=IF(E4<>"",TODAY()>EDATE(E4,6))

If you want Green for dates less than 6 months old, use:

=IF(E4<>"",TODAY()>EDATE(E4,-6))

Note: You should apply the rules in the order listed in this post, i.e >12, >6, >-6. When using CF, if more than one rule applies the same kind of formatting, such as a Fill color, the first rule that is TRUE take precedence. If you have the >6 month rule first, it will fill all cells 6 months or older with Yellow and not continue checking for dates that are 12 months older.

The reason I added IF(E4<>""...) is to allow you to leave a cell blank without the CF being applied. Without the IF(E4<>""...) Excel may consider a blank cell to be 1/0/1900 and apply the "older than 12 months" CF.

To apply the CF to a range of cells, select the entire range and use the rules suggested above. The rules will be applied to each cell in the range individually.

I don't think you want to apply the CF across the entire sheet, unless you will have dates in every cell. You should only apply the CF to the cells that you need to check.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 3, 2013 at 13:15:05
Thanks a bunch DerbyDad, my final question is if the date is not in the E4 cell do I just change that part of the formula to reflect which cell has the date??

Report •

#5
April 3, 2013 at 13:23:33
Yes, assuming you have the correct range selected so that the CF gets applied to the correct cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 3, 2013 at 13:43:13
Okay DerbyDad, what have I done wrong I used the formula from post #3, but when I change the date in the cell it remains green even if I put in a date from last year eg. today's date being 4/3/13 and I enter in the cell 4/3/2012 that cell stays green. Any idea as to why? or how to adjust that?

Report •

#7
April 3, 2013 at 15:10:40
You said "I used the formula from post #3"

There are 3 formulas I post #3. I assume you mean that you used them all, correct?

What order are they in? When you open the CF wizard, the colors should be Red, Yellow, Green. As I mentioned in post # 3, if more than one rule applies the same kind of formatting, such as a Fill color, the first rule that is TRUE take precedence.

If you have the Green rule first, =IF(E4<>"",TODAY()>EDATE(E4,-6)), then the cell will always be Green because TODAY() will always be greater than any past date that is in the cell.

Keep in mind that there is a difference between entering the rule in the wizard and applying the rules to the cells. When you use the "New Rule" button to add a rule, it puts it at the top of the list. That may not be the order in which you want them applied. Use the Up and Down arrows in the wizard to get the rules in the order you want them:

Red
Yellow
Green

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 3, 2013 at 15:13:39
Hey thanks a bunch it was just a matter of putting them in the correct order like you said. and yes I mean I was using all 3 formulas. it's working wonderfully. Again thanks for your help

Report •

Ask Question