ID Number to birthday date

[grahamward]

Hi Forum. I have a database of ID (social security) numbers, which also represent the persons birthdate. For example the ID number 6803126300186, would mean that the persons birthday is 12 March 1968. Would anyone know how to extract this from Excel, so that a date can be displayed for multiple ID numbers?

 

[grahamward]

Hi
How about
12/03/1968

=RIGHT(C1,2)&"/"&MID(C1,3,2)&"/"&19&19&LEFT(C1,2)

Or

=RIGHT(C1,2)&" "&TEXT(MID(C1,3,2)*29,"mmmm")&" "&19&19&LEFT(C1,2)

Thank you very much. Will give it a go.

 

[grahamward]

@mohadin Have you tested either of your suggestions.

@grahamward Could you have dates that are in the year 2000 or since?

Yes. We could have dates which are pre 2000 as well as after 2000

 

[Fluff]

Ok, in that case try the formulae in post#3 & post#9

 

[grahamward]

Hi Mohadin. The formula you gave worked. Thank you. The problem is that I cannot sort the text in date order. Any suggestions to solve this?

 

[Fluff]

The formula you gave worked.

Which one? I don't think any of them work.

 

[grahamward]

Which one? I don't think any of them work.

They worked for me with changes to the , to a ;

 

[Fluff]

Which one?

 

[mohadin]

=DATE(19&LEFT(LEFT(A1,6),2),RIGHT(LEFT(A1,6),2),MID(LEFT(A1,6),3,2))

 

[Fluff]

That still does not take into account that some dates will be in the 2000s.
Phuoc's formula in post#9 is the only one that is reliable as mine will return 2029 is the 1st two digits are 29

 

[dave3009]

Another option

=--TEXTJOIN("/",,MID(A2,{5,3,1},2))