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.
<tbody>
</tbody>
How can I convert the dates correctly to UK format for the entire date 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).
<tbody> </tbody> |
<tbody>
</tbody>
How can I convert the dates correctly to UK format for the entire date column ?