Mix of US and UK dates - how to fix?

[TheWennerWoman]

Hello,

I have inherited a file which has a mixture of US and UK dates - the locale of this system is UK.

So I have data like 31/03/2020 but also 2/28/2020. The US dates are aligned to the left and the UK ones to the right.

How do I fix? I've read about text to columns, formatting the cells and stuff like that but none of it works.

 

[Snakehips]

​

As a reminder .Formula = .Value2 converts automaticly US dates to local dates and formats the cells as Date as well …​

Indeed it does! Thanks for that new insight!

Maybe OP needs like below as a code.

Sub Conv_Date2()
Application.ScreenUpdating = False
Lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("D2:D" & Lastrow).Formula = Range("D2:D" & Lastrow).Value2
Application.ScreenUpdating = True
End Sub

 

[Marc L]

​

Yes it's a well known solution since last century as VBA knows only US text dates !​

​

Another trick with dates as text in the local date format : use FormulaLocal - even Value - instead of Formula …​