how to get correct AGE from identity card number

[jim_chan]

Hi guys, 

Please help me out. 

1)  i m trying to use this script to get the correct year 19xx, instead the wrong year 20xx

=date(date#(left('630502101234',6),'YYMMDD')) 

The result is 2/5/2063. my expected result should be 2/5/1963

2) i m trying to use back the same script to get the AGE, but i got a negative age, is -26

=Age(Today(),date#(left('630502101234',6),'YYMMDD'))

Thanks.

Jim

[jpenuliar]

This is not fool proof, but my take is:
If Left('630502101234',2) > 19 then use 1900s else 2000s.
e.g .
value = 100502101234
2 possible values = 1910 or 2010
if formula returns 1910, person is about 109 years old (not that it is impossible).
If formula returns  2010, person is about age 9

[jim_chan]

Thanks. But do you have a workable formula?

[jpenuliar · Accepted solution]

Try this code:

If(left('030502101234',2) > (Year(Today(1)) - 2000)
,MakeDate(1900 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
,MakeDate(2000 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
)

play around with different values

[jim_chan]

Hi, 

This part- If(left('030502101234',2), need to add and date() or date#, else it will only return value '03' rite?

Jim

[simrankaur]

=date(date#('19' & '' & left('630502101234',6),'YYYYMMDD'))

[jim_chan]

63 is just 1 if the data I have , how bout is 00, or 11?

[jpenuliar]

if you try playing around with the formula like below:

left('030502101234',2)
03

Date(left('030502101234',2))
03/01/1900

Date#(left('030502101234',2))
03

having the results in number form makes it easier to troubleshoot and perform maths

2019 - 2000 = 19 

2000 + 03 = 2003 

[jim_chan]

Thanks. Let me try it out. Will come back to you. 

[jim_chan]

Hi, 

i have tested it, the logic works for me! thank you very much for coming out with this solution.

Thanks!

Rgds, 

Jim Chan