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?

US Date		UK Date
2/28/2018		28/02/2018
3/01/2018		01/03/2018
3/30/2018		30/03/2018
3/31/2018		31/03/2018
4/01/2018		01/04/2018
4/02/2018		02/04/2018
4/03/2018		03/04/2018
4/18/2018		18/04/2018
12/11/2018		11/12/2018
4/30/2018		30/04/2018

<tbody>
</tbody>

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