VBA Change text column to date format

[davidmg1982]

Im having troubles to convert a text column into dates, but so far my script is just giving me back some crazy numbers only. Thanks ahead for your guidance.

This is how my data looks like:

01312020

02292020

03312020

04302020

05312020

This is what i want in return, along with the date format in the column

01/31/2020

02/29/2020

03/31/2020

04/30/2020

05/31/2020

Sub FormatDate()
With ActiveSheet.UsedRange.Columns("DO").Cells
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "mm/dd/yyyy"
End Sub

 

[alansidman]

Here are two solutions for you.
1. VBA

Option Explicit

Sub Dates()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        Range("A" & i) = Left(Range("A" & i), 2) & "/" & Mid(Range("A" & i), 3, 2) & "/" & Right(Range("A" & i), 4)
    Next i

End Sub

2. Power Query--Mcode follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 4}, true), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Column1.1", type text}}, "en-US"), "Column1.1", Splitter.SplitTextByPositions({0, 2}, true), {"Column1.1.1", "Column1.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column1.1.1", Int64.Type}, {"Column1.1.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.2", type text}}, "en-US"),{"Column1.1.1", "Column1.1.2", "Column1.2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}})
in
    #"Changed Type2"

Book1
	A	B	C
1	1/31/2020		Merged
2	2/29/2020		1/31/2020
3	3/31/2020		2/29/2020
4	4/30/2020		3/31/2020
5	5/31/2020		4/30/2020
6			5/31/2020
7
Sheet1

 

[mse330]

Another VBA without loop ...

Sub test()
  With ActiveSheet.UsedRange.Columns("DO")
    .Value = Evaluate(Replace("if(@="""","""",left(@,2)&""/""&mid(@,3,2)&""/""&right(@,4))", "@", .Address))
    .NumberFormat = "mm/dd/yyyy"
  End With
End Sub

 

[davidmg1982]

Both of your answers fixed my problem, thank you so much.

 

[mse330]

Glad to help & thanks for your feedback

 

[DanteAmor]

Here another macro for you to consider:

Sub ChangeDate()
  With Range("DO1", Range("DO" & Rows.Count).End(3))
    .Value = Evaluate("=IF({1},REPLACE(REPLACE(" & .Address & ",5,0,""/""),3,0,""/""))")
    .NumberFormat = "mm/dd/yyyy"
  End With
End Sub

 

[alansidman]

thanks for the feedback