vba range.replace not setting date format properly

[Mushy peas]

I have a range (let's call it the A column) that has dates in this format: dd.mm.yyyy so I want to change the format to short dates d/m/yyyy

Instead when I use range.replace, it takes the values as if it is in the mm/dd/yyyy format. How do I replace it.

Sub format_dates()
    Sheet1.Columns(1).Replace ".", "/"
End Sub

 

[RoryA]

You could try TTC:

    Sheet1.Range("A:A").TextToColumns Destination:=Sheet1.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

for example.

 

[Mushy peas]

You could try TTC:

    Sheet1.Range("A:A").TextToColumns Destination:=Sheet1.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

for example.

Omg, that somehow worked. Thanks so so much. Though may I ask how was TTC able to recognise the format?

 

[RoryA]

The fieldinfo argument tells it that column 1 is a date in DMY order.