Enter a YYYY in A1, compare to current year in A2

[skadee]

I'd like to enter a year in A10 (as YYYY), and have D1 indicate True if A10 shows a year more than 10 years old.

D4 calculates the date 10 years ago [ =DATE(YEAR(NOW())-10, MONTH(NOW()), DAY(NOW())) ]
D10 is to show "True" if A10 is more than 10 years ago from now.

I'm using B10 to convert the YYYY value to a date, and A10's value will always be entered as a year (I don't want the user to enter 1/1/2012 when it's simpler to enter "2012" alone).

I've tried re-formatting cells, using DATE, YEAR, DATEVALUE, but can't seem to get any combination to do what I'd like! Any advice, or am I stuck having the user enter 1/1/YYYY every time?

D5: =DATE(YEAR(NOW())-10, MONTH(NOW()), DAY(NOW()))
B10: =DATE(A10,1,1)
C10: =YEAR(DATE(A10,1,1))
D10: =IF(C10<YEAR($D$4),TRUE)

 

[skadee]

Looks like I can't edit my thread title, but I'm not using A1 and A2 in the sample.

 

[Jeffrey Mahoney]

Can you use this?
Cell D10
=YEAR(TODAY())-A10>=10

Make sure that Cell A1 is formatted as a number and not text

 

[Scott Huish]

Your formula in D4 could be greatly simplified to:
=EDATE(TODAY(),-120)

 

[Peter_SSs]

Welcome to the MrExcel board!

Also, I assume the formulas listed for B10 and C10 should actually refer to A10 not A2.
If that is the case, the formula in C10 is pointless as it will always return exactly the same value as A10

and have D1 indicate True if A10 shows a year more than 10 years old.

Why do you say "Fails to calculate difference" for the D10 formula since 2005 is well more than 10 years old?
Your existing D10 formula could also be written more simply without the IF as

=C10<YEAR($D$4)

Make sure that Cell A1 is formatted as a number and not text

It wouldn't really matter as your formula, and the OP's, work just as well if col A is Text.

 

[skadee]

Can you use this?
Cell D10
=YEAR(TODAY())-A10>=10

Make sure that Cell A1 is formatted as a number and not text

That works! Thanks!

 

[skadee]

Your formula in D4 could be greatly simplified to:
=EDATE(TODAY(),-120)

Thanks, I wasn't aware of the EDATE function!

 

[skadee]

Welcome to the MrExcel board!

Also, I assume the formulas listed for B10 and C10 should actually refer to A10 not A2.
If that is the case, the formula in C10 is pointless as it will always return exactly the same value as A10


Why do you say "Fails to calculate difference" for the D10 formula since 2005 is well more than 10 years old?
Your existing D10 formula could also be written more simply without the IF as

=C10<YEAR($D$4)

....

Yes, correct on all accounts. I moved items around on the sheet to better illustrate, but neglected to update the text versions of the formulas.

The indication of the failure to calculate was that I would get a VALUE error, or all ########'s.

It appears to be functional with the suggested solutions today, I was certainly getting myself down a rabbit hole for something that seemed as though it would be simple!

Thanks all!

 

[Peter_SSs]

Glad it is sorted in the end.