Conversion Of An Id Number To The Persons Age?

[excel in]

I'm a 1st-time user, and I would like to thank you in advance for a formula which would help in future

My question relates to a column of ID no's with total of 13 numerals eg:5807085206087
I would like to convert this to the persons age eg: 57 yrs.

The first 6 numerals describe the birthdate,the first 2 numerals 58 (1958) the 2nd two numerals are the month (07) which is July and the (08) is the day.

I have a list of 3500 and I need to insert a formula to carry out this function in Excel 2013.

Could anybody assist with a formula

Many thanks and forever indebted to you for your assistance

 

[jim may]

Assuming you don't have anyone less than 16 years old in the file... give this a shot..
We can get around this by expanding the formula to test for that if needed...


Excel 2010

	A	B
5807085206087

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]7/8/1958[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=DATE("19" & LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

With the same proviso as Jim, you could expand that formula to give the age directly.

Excel Workbook
	A	B
1	5807085206087	57
Age

 

[jim may]

Good catch Peter.. I all too frequently miss what the OP actually wants, in this case "age". I only saw the need to compute "birthday" -- LOL !! Jim

 

[pgc01]

Just 2 cents: adding MOD( ,100) makes it also valid for birthdates after 2000.

 

[shg]

Another way, integrating pgc's suggestion:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

ID​

[/td][td="bgcolor:#F3F3F3"]

Age​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

5807085206087​

[/td][td]

57​

[/td][td]B2: =MOD(DATEDIF(TEXT(INT(A2/10000000), "1900-00-00"), TODAY(), "y"), 100)[/td][/tr]
[/table]

 

[pgc01]

Another one, without DateDif():

=MOD(INT((TEXT(TODAY(),"aammdd")-(LEFT(A2,6)))/10000),100)

 

[shg]

I needed to change aa to yy, but that's very clever.

 

[pgc01]

Shg, thank you. I forgot to change my local code for year when I posted.

 

[excel in]

Hi Peter, The age 57 is exactly what I'm looking for. The id No. is in A1 and I pasted your formula in B1, but unfortunately I am not getting your answer of 57 in B1. The formula is only showing. What am I doing incorrectly.
Thank you
Gerry