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.

 

[AKOsman1]

Hi Dave, Thank you for your response. Unfortunately, I would not know what to do with the VBA code that you have sent me. I am absolutely new to VBA and have no idea what to do with this code. Can you assist, Please.

 

[Fluff]

My post of the VBA does not show properly.

You didn't post any VBA, just the XL2BB code inside VBA tags.

 

[Dave Patton]

My apologizes, I was just trying to help.
"Hi Dave, Thank you for your response. Unfortunately, I would not know what to do with the VBA code that you have sent me. I am absolutely new to VBA and have no idea what to do with this code. Can you assist, Please."

See Peter's VBA in post #13. I assumed that you had tried this VBA!

I suggest that you change the row with .NumberFormat = "@" to to .NumberFormat = "yyyy-mm-dd"

To ensure that the results are real dates, format the dates to a different format and endure the information is correct or look at the value of the cells in the post.
Row 3 is a copy of Row showing the result With Number format General.
If you prefer the format of yyyy-mm-dd, then just change the format back to that format. You just need to look at a few.

Dates Time.xlsm
	A	B	C	D	E
1		Dates	Text	Date
2	470527ab12efg	1947-05-27	1947-05-27	27-May-47
3	470527ab12efg	17314	1947-05-27	17314	Number Format General
4
5	500113ab12efg	1950-01-13	1950-01-13	13-Jan-50
6	000630ab12efg	2000-06-30	2000-06-30	30-Jun-00
7	050114ab12efg	2005-01-14	2005-01-14	14-Jan-05
8
4i

Cell Formulas
Range	Formula
C2:C3,C5:C7	C2	=TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00")
D2:D3,D5:D7	D2	=--(TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00"))

 

[Peter_SSs]

I think that your excel version (2007) & how it uses "Evaluate" may be the issue as suggested by @Dave Patton.
Try this code (in a standard module)

Sub Extract_Date_Text_v2()
  Application.ScreenUpdating = False
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "yyyy-mm-dd"
    .Formula = "=--TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),""0-00-00"")"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub

 

[AKOsman1]

Thanks Peter, the code works 100% correctly. I sincerely appreciate your help and the effort by whoever responded.

 

[Peter_SSs]

You're welcome. Glad we got there in the end.