Why is my string variables not concatenating-CodePudding

I have data which I have extracted from a CSV. In a cell is a date YYYYMMDD and I need it to be "DD/MM/YYYY" For example 22020705

For i = 1 To lastRow
cbhDate = Cells(i, 62)
resultYear = Left(cbhDate, 4)
resultDay = Right(cbhDate, 2)
resultMonth = Mid(cbhDate, 5, 2)
Cells(i, 62) = resultDay & "/" & resultMonth & "/" & resultYear

Next i

For some reason the output in the cell is 7/05/2022 instead of 05/07/2022. The number format in the column changes automatically to Date. My PC is set to Australian date settings.

if I add

Cells(i, 62).NumberFormat = "DD/MM/YYYY"

to the end of this setion of code, it does not make a difference.

TIA

CodePudding user response：

Try This.

Sub ChangeDateFormat()

    Dim Lastrow As Integer
    
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'Change this column as needed
    
    For i = 1 To Lastrow

        Cells(i, 62).NumberFormat = "dd/mm/yyyy"
            
    Next i

End Sub

CodePudding user response：

You can use Format and DateValue to convert the text date to a true date:

Dim i As Long
Dim r As Range

For i = 1 To lastRow
    Set r = Cells(i, 62)
    r.Value = DateValue(Format(r.Value, "@@@@\/@@\/@@"))
    r.NumberFormat = "dd/mm/yyyy"
Next

Set r = Nothing