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?

 

[mohadin]

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)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=--TEXTJOIN("/",,MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))

 

[Phuoc]

Or try this:

=--TEXT(19&LEFT(C1,6),"0000-00-00")

 

[Fluff]

@mohadin Have you tested either of your suggestions.

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

 

[mohadin]

Sorry
Try this One

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

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

 

[mohadin]

Have you tested either of your suggestions.

 

[Fluff]

Still not sure why you have the &19 in there twice.

 

[Phuoc]

Book1
	C	D
1	6803126300186.00	3/12/1968
2	1003126300186.00	3/12/2010
3	0003126300186	3/12/2000
4	2403126300187.00	3/12/1924
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=--TEXT(IF(LEFT(C1,2)>TEXT(TODAY(),"yy"),19,20)&LEFT(C1,6),"0000-00-00")

 

[mohadin]

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

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