Right align dates

[tiredofit]

The problem I have is exactly as described in this article:

https://www.excelforum.com/excel-general/702292-alignment-of-dates.html

What seems to be a date is actually text, so correct it, the suggestion was to use the Text To Columns function.

It worked manually, so I recorded my steps to see the code:

 Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

However, when I ran the code, the heading moved up from row 7 to 6.

Can someone please tell me what's wrong.

Thanks

_{^{<strike>
</strike>}}

 

[tiredofit]

Other times, nothing happens.

 

[tiredofit]

This seemed to do the trick:

https://stackoverflow.com/questions/28516923/excel-date-formatting-not-working

with this code:

Sub mdy_2_dmy_by_Sel()
    Dim rDT As Range
    With Selection
        .Replace what:=Chr(160), replacement:=Chr(32), lookat:=xlPart
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        For Each rDT In .Cells
        Application.EnableEvents = False
            rDT = CDate(rDT.Value2)
            Application.EnableEvents = True
        Next rDT
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub​

​

_{^{<strike>
</strike>}}