Formatting dates in US format to UK format

[Ozhatch]

I have a sheet of data which has dates in the US style, i.e. mm/dd/yyyy. How would I go about re-formatting them into the UK style, i.e. dd/mm/yyy?

[TABLE="width: 174"]
<tbody>[TR]
[TD]US Date[/TD]
[TD][/TD]
[TD]UK Date[/TD]
[/TR]
[TR]
[TD]2/28/2018[/TD]
[TD][/TD]
[TD]28/02/2018[/TD]
[/TR]
[TR]
[TD]3/01/2018[/TD]
[TD][/TD]
[TD]01/03/2018[/TD]
[/TR]
[TR]
[TD]3/30/2018[/TD]
[TD][/TD]
[TD]30/03/2018[/TD]
[/TR]
[TR]
[TD]3/31/2018[/TD]
[TD][/TD]
[TD]31/03/2018[/TD]
[/TR]
[TR]
[TD]4/01/2018[/TD]
[TD][/TD]
[TD]01/04/2018[/TD]
[/TR]
[TR]
[TD]4/02/2018[/TD]
[TD][/TD]
[TD]02/04/2018[/TD]
[/TR]
[TR]
[TD]4/03/2018[/TD]
[TD][/TD]
[TD]03/04/2018[/TD]
[/TR]
[TR]
[TD]4/18/2018[/TD]
[TD][/TD]
[TD]18/04/2018[/TD]
[/TR]
[TR]
[TD]12/11/2018[/TD]
[TD][/TD]
[TD]11/12/2018[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD][/TD]
[TD]30/04/2018[/TD]
[/TR]
</tbody>[/TABLE]

In addition, many of the US dates don't seem to be recognised as dates by Excel. This is evidenced by the fact that if I convert a column of dates to a number format, some dates don't change. e.g. 9/10/2018 (US format) becomes 43382, but 9/18/2018 (US format) remains unchanged.

Any help much appreciated.

Have a happy 2018!

 

[Dave Patton]

try Data | Text to Columns Next Next Date MDY destination A2

Then format to your preference

 

[Ozhatch]

Perfect! Thank you very much.

​

 

[Dave Patton]

Thanks for the feedback.

Dave