I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first (flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900+100 *(DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=TODAY ())+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR(NOW ()),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I am guessing no one in your country will ever live to be more than 100?
Here is a formula that seems to work for those less than 100 years old...

=DATE(1900+100*(IF(LEFT(A1,2)<=RIGHT(YEAR(NOW()),2 ),IF(--MID(A1,3,2)<=MONTH(NOW()),IF(--MID(A1,5,2)<=DAY(NOW()),1,0),0),0))+LEFT(A1,2),MID (A1,3,2),MID(A1,5,2))

Rick


"Hennie Neuhoff" wrote in
message ...
In our country all citizens have a unique [13digit] identity number, the
first 6 digits being date of bitrh. I've tried to convert these 6 digits
to
date of birth, without
any luck. The main problem being the year of birth forms the first 2
digits,
ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
digits] .
Any help would be appreciated
HJN