US TO UK text to columns not working

[thedeadzeds]

Hi Guys,

I have the following SharePoint list exported into excel. The dates are showing as per below which is US. I need to convert this to UK dd-mm-yy. Everything I have tried has failed, text to columns will convert some but not all of the dates. Can anyone help this is driving me mad

12/4/2020 11:57:24 AM

 

[thedeadzeds]

Hi Guys,

I have the following SharePoint list exported into excel. The dates are showing as per below which is US. I need to convert this to UK dd-mm-yy. Everything I have tried has failed, text to columns will convert some but not all of the dates. Can anyone help this is driving me mad

12/4/2020 11:57:24 AM

Just to clarify the date should be 4th December 2020 in the format 04/12/20 not 12th April 2020

 

[thedeadzeds]

I have also tried =TEXT(A3,"mm/dd/yyyy") which as you can see works fine with the to last date and time, but does not convert the top one

12/15/2020 10:38:48 AM	12/15/2020 10:38:48 AM
12/9/2020 3:59:51 PM	09/12/2020

The dates in yellow will not convert