excel - VBA change date text to date format

Question

I have an exported excel with date formated this way 24.12.2019 , how can i use VBA and set a macro so that when i click on macro it becomes 2019-12-24. I tried to find in stackoverflow for related post, but to unavail. Both are general text format and i want to keep it that way.

Answer 1

Try the following:

Sub Test()

Dim lr As Long
Dim rng As Range

With Sheet1 'Change according to your sheet's codename
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row 'Change "A" to whichever column data is in
    Set rng = .Range("A1:A" & lr) 'Change accordingly again
    rng.TextToColumns Destination:=rng, DataType:=xlFixedWidth, FieldInfo:=Array(0, xlDMYFormat)
    rng.NumberFormat = "YYYY-MM-DD"
End With

End Sub