Text to Date Conversion

[peelamedu_bulls]

I have a column that has an extra "1" at the start. The requirement is to strip that extra character/number and convert the date to a MM/DD/YYYY format.

Column A
1140430
1141124
1140403

Column A
14/04/2030
14/11/2024
14/04/2003

is there a VBA way to do this, preferably without creating any new column and do this manipulation in the same Column A

 

[Kenneth Hobson]

Here is the link to my test file, FWIW. I added a bit more so that the results could be seen for each run.
https://www.dropbox.com/s/5quumta4xf6iz66/TextToDateAndDateFormat.xlsm?dl=1

For time runs, if a routine is less than 10s, it suits me. In 1990, I did a POV ray tracing run that took 5 days on an IBM XT with a math co-processor and maxed out ram, 64kb.

Sub AvgRunTimes()
    Dim d As Double, dd As Double
    Dim s As String
    Dim j As Integer, k As Integer
    Dim jj As Integer, kk As Integer
    
    jj = 4   'Number of single Sub runs.
    kk = 3 'Number of replicate runs
    
    For k = 1 To kk 'Replicate runs
        For j = 1 To jj 'Single runs
            'Number of choices should be jj, if not, change value of jj.
            's = "Txt2Date" & Choose(j, 3, 5, 6, 10)
            s = "Txt2Date" & Choose(j, 10, 3, 5, 6)
            Fill100kRows s, 1
            d = Timer
            Application.Run s
            dd = Timer
            Debug.Print s, dd - d & " seconds."
            MoveColARightEnd
        Next j
    Next k
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Sub Fill100kRows(head As String, col)
    Cells(1, col) = head
    Range(Cells(2, col), Cells(100001, col)).Clear
    Cells(2, col) = ""
    Cells(3, col) = 1140499
    Range(Cells(4, col), Cells(100001, col)) = 1140403
End Sub

Sub MoveColARightEnd()
    Dim r As Range, i As Integer
    i = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Set r = Intersect(ActiveSheet.UsedRange, Range("A:A"))
    r.Cut Cells(1, i)
    Application.CutCopyMode = False
End Sub