DOB from ID number

[MarkCBB]

Hi there,

I am looking for a formula to workout the DOB (In date format) of an Id number, been playing around with LEFT, RIGHT,TEXT, but so far no luck.

 

[GTO]

Might we have a peek at some sample data?

 

[Joe4]

What do you mean?
Is there correlation between the two? If so, what is it?

Maybe you could post an example with your expected result.

 

[MarkCBB]

The ID number of a person contains thier DOB in the first 6 numbers. i.e. 8604171234567
the DOB of that ID is 17/04/1986.

here is some more sample data:

5101011412088 - DOB should be 1/1/1951
6501280416083 - DOB should be 28/1/1965
5406061279083
8702200579087
6309090874081
8406295862081

 

[Scott Huish]

=TEXT(MID(A1,3,4)&LEFT(A1,2),"00\/00\/00")+0

Format as d/m/yyyy

 

[Jonmo1]

Perhaps.

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

 

[Joe4]

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

and format cell in desired date format.

 

[MarkCBB]

Hi there,

HOTPEPPER, yours returned an Error, #Value, not sure why, probably me doing something wrong.

jonmo1 & Joe4, your formula works.

thanks to all 3 of your for your speedy responses.

 

[barry houdini]

It probably depends on your regional settings - this version with TEXT function should work for you

=TEXT(LEFT(A1,6),"00-00-00")+0

format as date

....this one also has the advantage (?) of recognising 21st century DOBs according to regional settings, e.g. with my settings years up to 29 will be interpreted as 21st century and years after 29 will be interpreted as 20th century