Formula to return a code as a year (eg 1998)

[Kopi$star]

I have a code in which the first 2 digits represent the year. I.E. 8040121011 would represent 1980. I understand that the function left(a1,2) would return 80 for 1980. However, I need to return 1980. Is there a way in which I can use another formula as well to combine with the left function and get 1980?

Thanks for your help.

 

[Excelsiordave]

Hi,

Try the following formula in the column after your =left(a1,2)....

=if(c1>12,19,20)

Then in column E...

=c1&d1

Cheers,

Dave.

 

[Jonmo1]

Try

=YEAR("1/1/"&LEFT(A1,2))


Note, the cutoff is 30 between 1900 and 2000

00 - 29 = 2000 - 2029
30 - 99 = 1930 - 1999