Thread: Autocalculate Age from Identity Card Number

How to do formula to autocalculate age from Identity Card Number?
e.g 110802018888 means the 1st 6 numbers is the birthday = 02/08/2011
e.g. 090802018888 means the 1st 6 numbers is the birthday = 02/08/2009.
e.g 980802018888 means the 1st 6 numbers is the birthday = 02/08/1998

I tried to do the formula, but not valid for year after 2000 or before 2000
=IF(LEN(K3)=11,12-LEFT(K3,1),(IF(LEN(K3)=12,112-LEFT(K3,2)))).

The file is uploaded.
Thank you very much.

You have a problem in that there is no way for a formula to differentiate between, say, 1911 and 2011, or 1909 and 2009 with only a two digit year field.


Regards, TMS

Agree..
But if you take result that nobody can be over 100 you can use this:

Another way would be to assume that you can't have an I.D. card before you are born, and you die before you get to one hundred!

=DATE(IF(LEFT(A2,2)*1<=YEAR(TODAY())-2000,2000+(LEFT(A2,2)*1),1900+(LEFT(A2,2)*1)),MID(A2,3,2)*1,MID(A2,5,2)*1)

My result return one extra year depending on date: If today date is 4.1.2012 then you'll get:


120103105716 result: 0
120106105717 result: 1

If Today's date is higher than IC date

My result return one extra year depending on date: If today date is 4.1.2012 then you'll get:


120103105716 result: 0
120106105717 result: -1

If Today's date is higher than IC date

Further on:

110103105716 result: 1
110106105717 result: 0

For every other year.

Edit: Rather minus than plus

In the example data, my understanding was that one of the ages was 101 ... but I might be mistaken. People are living longer these days.

Regards, TMS

If so then, as you said, no way to determine is it 1 or 101.
This is up to 99

I think zbor we might both be wrong!

See the new formulae in columns B:C in this workbook

Is there a minimum age for an ID card requirement?

Yea, I've edited attachment at 12:05. You got previous one.. change + into - after first formula.

Aye, the forum has been crashing on me all day, it's hard to keep up.
I noticed that you posted one reply twice, did you get a warning about "Navigating away from the page" when you first tried to post?

This workbook has your formula corrected for the record, and a possible minimum age (5) for holding an ID card solution.
I realise that this doesn't give the result that the OP asks for but it covers an age group from 5 to 104.

Just a thought.


[EDIT]
This post crashed, but sent the text without the attachment ...

My 1st uploaded file might be with wrong formula to calculate the age that's why it return for 101 age instead of 1 year old of age. It should return as 1 year old of age.
There is no minimum age in here (Malaysia) to make Identity Card Number.
Rarely people lives more than 100 years of age in here.
e.g. 110106105717 means the birth date 06/01/2011 (1st 6 numbers) rarely to see people born on the year 1911 with ID number anymore.
Thank you everybody for your help.
I must add in the DOB next to Identity Card Number and to get the actual Age (Below 100 years old).
Thanks again