date functions using entered year only

[Beaker]

I am using excel 2007 and WinXP.
I am creating a chemical database including MSDS (material safety data sheets) which need to be no more than 5years old. I am trying to write a formula such that a year can be entered (e.g. 2007) and in the next cell will be 'yes' or 'no' depending on the answer to 'current year' -'year entered' (eg 2010-2007) using some logic.
I know how to formulate IF, THEN etc. Its just that when I enter a year it treats it like a serial number, converting 2005 to 1905. I would rather not have to enter a complete date, just the year only. (I understand the NOW() function for current date too.)

Thanks for all help.

Andrew

 

[hiker95]

Beaker,

Excel Workbook
	A	B
1	Current Year	Answer
2	2007	Yes
3	2006	No
4	2010	Yes
5	2011	No
6
Sheet1

The formula in cell B2 copied down:
=IF(OR(A2=2007,A2=2008,A2=2009,A2=2010),"Yes","No")

 

[AdamL]

Or

=IF((YEAR(TODAY())-A2)>5,"No","Yes")

 

[barry houdini]

If you enter a year and it's displayed as a date in 1905 then that's just a formatting issue (it doen't affect the value or any formula based on the value), just format the cells as general.

So presumably 2005 is OK but 2004 isn't?

If you put the year in A2 try this formula

=IF(YEAR(TODAY())-A2>5,"Too old","OK")

 

[hiker95]

Beaker,

Maybe this?

Excel Workbook
	A	B
1	Current Year	Answer
2	2007	Yes
3	2006	Yes
4	2005	No
5
Sheet1

The formula in cell B2 copied down:
=IF(A2<=YEAR(NOW())-5,"No","Yes")

 

[Beaker]

Thanks for your replies. Pairing the YEAR function with TODAY function did the trick.

Thanks again.