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

Kopi$star

New Member
Joined
Jul 6, 2012
Messages
19
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Excelsiordave

Board Regular
Joined
Jan 18, 2010
Messages
52
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

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


Note, the cutoff is 30 between 1900 and 2000

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

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top