I just ran 520210987654 through Data>Text to Column>Fixed Width.

Select first 6 numbers and Column Data Format>YMD.

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\)"

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

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

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