Full Date of Birth YYYY-MM-DD from 6 char. String YYMMDD

[AKOsman1]

I have a 13 Character ID No. The first 6 characters represent the Date of Birth (YYMMDD). I want to display the date as YYYY-MM-DD eg;
470527 = 1947-05-27
500113 = 1950-01-13
000630 = 2000-06-30
050114 = 2005-01-14
Please show me how to achieve this.

 

[RustamMika]

AKOsman1​

Efendiç
What about
220124 ?
Is it 1922-01-24 or 2022-01-24

 

[AKOsman1]

Could be either. If it is 1922 the person would be 99 years old. If it is 2022 the person would be 1 year old. Neither would affect my system since they are both unemployable. A reasonable range to look at would be from 1945 to 2010.

 

[RustamMika]

Please check.

 

[Asbestos_Jen]

The original post said the IDs were 13 characters, so the formula should use two "mid" functions instead of a mid and a right.

Book1
	B	C	D	E	F
2	"1945 to 2010"		13 characters		YYYY-MM-DD
3	470527 = 1947-05-27		470527ab12efg		1947-05-27
4	500113 = 1950-01-13		500113ab12efg		1950-01-13
5	000630 = 2000-06-30		000630ab12efg		2000-06-30
6	050114 = 2005-01-14		050114ab12efg		2005-01-14
Sheet1

Cell Formulas
Range	Formula
F3:F6	F3	=DATE(IF(VALUE(LEFT(D3,2))>=45,19&LEFT(D3,2),20&LEFT(D3,2)),MID(D3,3,2),MID(D3,5,2))

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1
2	470527ab12efg	27/05/1947
3	500113ab12efg	13/01/1950
4	000630ab12efg	30/06/2000
5	050114ab12efg	14/01/2005
Data

Cell Formulas
Range	Formula
B2:B5	B2	=--TEXT(IF(LEFT(A2,2)>"45",19,20)&LEFT(A2,6),"00-00-00")

 

[Peter_SSs]

@RustamMika
When suggesting formulas please either ..

• Post your sample with XL2BB, or
• Post you formula directly in your post using the formula code tags
  
  , or
  
  
• Post your formula as plain text directly in your post

.. so that the OP (& other readers) can actually copy your formula suggestion for testing

@Akos
If you are happy with a text result then try column B below. If you want actual dates, try the slight shortening of @Fluff's formula in column C and format the column with the Custom date formatting of "yyyy-mm-dd"

23 11 24.xlsm
	A	B	C
1		Text	Date
2	470527ab12efg	1947-05-27	1947-05-27
3	500113ab12efg	1950-01-13	1950-01-13
4	000630ab12efg	2000-06-30	2000-06-30
5	050114ab12efg	2005-01-14	2005-01-14
Extract Date

Cell Formulas
Range	Formula
B2:B5	B2	=TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00")
C2:C5	C2	=--(TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00"))

 

[AKOsman1]

Absolutely Genius...How do I achieve the same result in Excel VBA?

 

[Peter_SSs]

How do I achieve the same result in Excel VBA?

1. Ask for that in your initial post so time isn't (perhaps) wasted developing formula suggestions.
2. Tell us which result you want as 2 different results were given in the marked solution.

 

[AKOsman1]

My sincere apologies. I forgot to mention in my initial request that I wanted the solution in Excel Vba code. I only realized my error after I received your answer in Excel only.