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

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!

 

12 responses to “DD/MM/YYYY to MM/DD/YYYY in Excel”

  1. AAA says:

    Thanks Team, its give me big support.

  2. Abiodun says:

    Thanks a lot for this. It is a Whao!

  3. Sherlock says:

    Very nice solution

  4. Sisay says:

    thank you my friend nice solution!!!

  5. prathap says:

    superb thank you

  6. Thank you very much

  7. Swapna Chopra says:

    Thanks a lot for great help. was looking for this solution from a long time.

  8. ravman01 says:

    thanks – that was a great help

  9. Saharat says:

    Many Thanks !!!

  10. prasadbabu18 says:

    This is an awesome solution. 🙂

  11. […] The dates in the exported document are in the mm/dd/yyyy format. If you wish to customize the document, you can find information on how to change the dates here. […]

  12. […] date format for the documents is mm/dd/yyyy. If you wish to customize the document, find out how here. The export will include the following fields for each […]