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.

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). 04/30/2015 23:49:50 30/04/2015 23:49:50 04/30/2015 23:59:50 30/04/2015 23:59:50 05/01/2015 00:09 09/42/0068 68287037 05/01/2015 00:19 09/42/0137 37731481 05/12/2015 23:49 43/42/9929 29398148 05/12/2015 23:59 43/42/9998 98842593 05/13/2015 00:09:50 13/05/2015 00:09:50 05/13/2015 00:19:50 13/05/2015 00:19:50 <tbody> </tbody>

<tbody>
</tbody>

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