Excel 2010 - change US dates to UK format

Question

I have imported a CSV file with 2 long columns of dates. These dates are in the US format Aug/28/2013 and I want them to be in the standard UK dd/mm/yyyy format.

I have tried formatting the cells as US dates and then converting them to number-only formats, and various other permutations within the Date format box, but with no success.

Can anyone rid me of these awful US dates please?

Answer 1

This can be tricky when the dates in mixed format eg. UK and US in the same column. I have found an effective if inelegant solution:

Step1) Select the column containing the dates to be converted;

Step2) Format, Cells, Text;

Step3) Format, Cells, Date, US;

Step4) Data, Text to column, Next, Delimited, Next, delete all delimiters, Next, select format MDY;

Step5) Format, Cells, Date, UK.

Step4 had been suggested elsewhere, but that on it's own didn't do it for me. I am hoping to combine these steps into a macro but no success this far.

Answer 2

Related to this, the below simply formula can be helpful for changing a date from

"MM/DD/YYYY"

into

"DD/MM/YYYY".

=VALUE(TEXT(B2,"mm/dd/yyyy"))

Answer 3

I couldn't get the most common answer to work, the process that worked for me was:

For date 10/04/2018 11:49:20, right-click cell and "Format Cells", "Number" tab and select "Custom" Category and then select mm/dd/yyyy hh:mm.

Answer 4

We can get best of both world with this more concise formula:

=IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))

Can't find anything shorter.