QUESTION: I have two cells. "Installed" and "Warning".
"Installed" contains just a year ie. 2001
I would like to have the cell "Warning" display either "Warning" or "Good".
"Warning" displayed if the date installed is 10 or more years older than the current date. But I need the sheet to always use the current date.
"Good" displayed if the date installed is less than 10 years old.
ANSWER: Justin Taylor,
In cell Warning put this formula
this assumed installed is a defined/range name that refers to the cell containing the year the item was installed.
---------- FOLLOW-UP ----------
QUESTION: It didn't seem to work but that might have been because I should have said "column" not "cell" in my question when referring to "Installed"
In some cases that would make a difference. However, I put years in B1:B whatever and I named that range Installed. In C1 I put in my formula and drag filled it down. It worked fine because even though Installed is a column range, since the formula is in a row that intersects that column range, the formula performs what is called implicit intersection. In row 1, where the formula sees Installed, it actually looks a the intersection of that column Name and row 1 and so it looks at cell B1. in row 5 it it looks at B5. In column D I put in a similar formula (for checking purposes) that explicitly refers to the proper cell and you can see from the attached image, they both produce the exact same result for me.
So I show in the image the formula works as I understand the requirement.
Note: I did not in the picture there were a couple of disagreements between the original formula and the check formula - that is because in the check formula I used <=10 by mistake. I have corrected it and they all match now. I have attached a revised image with
B1:B28 Installed named range with install year
C1:C28 My original formula placed in C1 and drag filled down
C1: =IF(Installed="","",IF((YEAR(TODAY())-Installed)<10,"Good","Warning")) filled down
D1:D28 My revised check formula D1: =IF(YEAR(TODAY())-B1<10,"Good","Warning") filled down
E1:E28 E1: =YEAR(TODAY())-B1 drag filled down
F1:F28 F1: =Installed drag filled down - demonstrate implicit intersection
G1:G28 G1: =YEAR(TODAY())-F1 drag filled down
so columns D - G are just formulas to show different parts of the calculation