Convert US dates to UK dates when import data from text (.csv file) does not work

[JennyReed]

I have a .csv file with dates in the first column.
The date format is US i.e. mm/dd/yyyy HH:mm:ss.

I am working with UK date format (dd/mm/yyyy HH:mm:ss) and need to convert the date format.
I have tried the following and neither works:

1) Data - From Text - (import .csv file) - Under Text Import Wizard: Delimited - Delimiters (semicolon) - Column data format (changed to MDY) - Finish.
I still get the dates left justified where the US dates have not been converted, right justified where excel thinks it has found a date and converted incorrectly and then right justified again where the dates have not been converted.

[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[TR]
[TD]2) I wrote in the formula:
=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4)&" "&RIGHT(A1,8)
This creates the correct 'date' 'text' for the 'unconverted dates' (black dates) but fails where the dates are converted incorrectly (green dates).
[TABLE="class: cms_table, width: 258"]
<tbody>[TR]
[TD]04/30/2015 23:49:50[/TD]
[TD="colspan: 2"]30/04/2015 23:49:50[/TD]
[/TR]
[TR]
[TD]04/30/2015 23:59:50[/TD]
[TD="colspan: 2"]30/04/2015 23:59:50[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2015 00:09
[/TD]
[TD="colspan: 2"]09/42/0068 68287037[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2015 00:19[/TD]
[TD="colspan: 2"]09/42/0137 37731481[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/12/2015 23:49[/TD]
[TD="colspan: 2"]43/42/9929 29398148[/TD]
[/TR]
[TR]
[TD="align: right"]05/12/2015 23:59[/TD]
[TD="colspan: 2"]43/42/9998 98842593[/TD]
[/TR]
[TR]
[TD]05/13/2015 00:09:50[/TD]
[TD="colspan: 2"]13/05/2015 00:09:50[/TD]
[/TR]
[TR]
[TD]05/13/2015 00:19:50[/TD]
[TD="colspan: 2"]13/05/2015 00:19:50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

How can I convert the dates correctly to UK format for the entire date column ?

 

[Trevor G]

VBA would work to use the local regional settings from your pc. Example using local is shown below:

workbook = workbooks.Open(filename, Local:= true)

 

[JennyReed]

VBA would work to use the local regional settings from your pc. Example using local is shown below:

Hi Trevor.
Thank you for your answer. I am afraid that I do not understand your answer. 'VBA would work.....'.
Can you explain how I would do this ?

Thank you
Jenny