What date do you think of with when you see the following date format:
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:
With the cell selected, I just went to Format -> Cells… Selected “Custom” and used MM/DD/YYYY to specify the Type:
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:
- Click Finish and you are done, the DD/MM/YYYY to MM/DD/YYYY conversion should take place: