DD/MM/YYYY to MM/DD/YYYY in Excel

What date do you think of with when you see the following date format:

16/3/2014

If you are in the United States, you are probably thinking “does not compute”.

That day format (DD/MM/YYYY) is the one used by my Costa Rican banks and it is the one that makes most sense to me (going from smaller units, days, to the largest unit, years).  When I feed the bank statements to my financing software, it chokes and labels everything opposite to what I want to in terms of months and dates.  I thought I could easily fix this within Excel, but it was not as straightforward as I thought.

So here is a spreadsheet with the “incorrect” data format, all I need is to convert it to 3/16/2014:

How hard can this be, really?

With the cell selected, I just went to Format -> Cells…   Selected “Custom” and used MM/DD/YYYY to specify the Type:

This didn’t do much

Clicking OK did absolutely nothing.  I tried entering every possible permutation I could think of with no luck.  This thread gave me the solution (if you know a better one, please post in comments).

• From the Data tab, select Text to Columns.
• You will be presented with a wizard, click the Next button twice.
• On the third wizard screen, under Column data format, select DMY as Date:

Overkill? Maybe, but it just works.

• Click Finish and you are done, the DD/MM/YYYY to MM/DD/YYYY conversion should take place:

Finally!