conversion of an id number to the persons age?

[Hans100]

I'm a 1st time user so I thank all here in advance for what I see will be a huge help for me in the comming weeks !

My question relates to a column of id no's which total 13 numerals eg:6406125128051
I would like to convert this to the persons age eg: 29 yrs.

The first 6 numerals describe the persons birthdate,therefore the first 2 numerals 64 (1964)need to be subtracted from 103 (2003)to give answer.

I have a list of 600 and I need to insert a formula to carry out this functio.
Could anybody assist in suppling me the procedure?
Many thanks

 

[phantom1975]

WELCOME TO THE BOARD!

Will this do?

Book1
	A	B	C	D
1	6406125128051	38.8653
2	7507231234567	27.7475
Sheet1

 

[Yogi Anand]

Hi Hans100:

Welcome to the Board!

Phantom in his post above has given you a more comprehensive age computation that takes the entire birth date (not just the birth year) into account.

If you want to tke just the birth year into your account as shown in your original question, then the following may of interest to you ...

Book1
	A	B	C	D
1	6406125128051	39
2	5406125128051	49
3	6206125128051	41
Sheet6

 

[Hans100]

Thank you very much Phantom & Yogi!
Im sorry but I will not be able to check back till tonite as I have to rush off now.I'll implement the formula and I get back to you later.
ps I presume since the id column is in col Q ,I must insert another column between Q & S and then substitute the A with a Q?

The worksheet has informationon 750 rows but only id's in 670 rows. Where do I exactly put that request in for those cells to be targeted?
Thanks once again
Go well .
Later
Hans

 

[Dave Patton]

You can try

=INT((TODAY()-DATE(LEFT(A1,2)+1900,MID(A1,3,2)+0,MID(A1,5,2)+0))/365.25)


or if DOB could be in this century revise something like

=INT((TODAY()-DATE(LEFT(A2,2)+1900+(LEFT(A2,2)+0<10)*100,MID(A2,3,2)+0,MID(A2,5,2)+0))/365.25)

Check which numbers are for month and which are for days and revise the above if necessary.

 

[Mark W.]

=DATEDIF(TEXT((LEFT(A1,2)<"30")+19&LEFT(A1,6),"0000-00-00")+0,TODAY(),"Y")

 

[francois23]

Re: WELCOME TO THE BOARD!

Hi there,
I live in Mauritius, we use the date format : DD/MM/YYY and our ID is as follows : J210282300185F -> 21 (day) - 02 (MM) - 82 (YY) 21-02-1982 is his birth date.
My question is I need your help to convert this person birth date to today's age which is 35 years old.

Can somebody help me please?

Thanks,
Francois