Jul 14, 2014

This is one of the most frequently asked questions that come up when I'm teaching Microsoft Excel courses.  Many people export their data from an accounting system or a database that originated in the US or Canada where dates are usually in the format of Month-Day-Year. Here in Australia, and much of the world use the format of Day-Month-Year.

No matter which version of the software you are using, you will want to use the 'Text to Columns Wizard' in Microsoft Excel. This is a wizard that will help us convert the date format, but once you start to play around with it, you'll find it can be useful for many other things.

First of all, let’s look at the American dates in Excel in the 'MMDDYY' format.

Even if we go and apply the date format from the 'Number' group, it won't recognise the dates. This is because if Excel is installed on a system here in Australia, it will have its regional settings set to the date format 'DDMMYY' and, therefore, it won't recognise the dates if the larger day portion is in the middle of the delimiters or forward slashes e.g. 05/25/14.

You could change your regional settings temporarily on your computer, and this is how some people choose to do it, but that seems like a bit of overkill to change the regional settings in the Control Panel and then have to turn them back again. I never know what else that may affect in other programs, so I don’t do that.

Others also suggest using a function and breaking the 3 sections into 3 columns then glue them or 'Concatenate' them back in the order that you want. Again, that is way too much work. I am all for the simple, short and easy way to do things.

So here is how you can easily convert American dates to the Australian format in Excel:

1. In Excel, select or highlight the cells with the dates that you want to change.
2. Click on the 'Data' tab and select the 'Text to Columns' button. It will ask you what kind of data you have to convert 'Delimited' or 'Fixed Width.' Is there something that will indicate where the columns should break? If yes, then you would tick 'delimited.' This is used for separating data into separate columns such as addresses where the street, suburb and postal code are all jumbled into the same column, but you need them separated for sorting of filtering or perhaps a mail merge data source. Since we don’t actually want to separate the content (we only want to change the order), we select the second option which says 'Fixed Width.' Once ticked, click 'Next.'
3. In the next step, you can choose to click into the content and place a line where the column should break or exclude any content, for example, taking a prefix off of a number. We don’t need to do anything here so click 'Next.'
4. Now you'll be asked what the format you want - General, Text or Date. We want to choose the 'date' option, but here is where the trick is. You need to choose 'Date' and then use the drop-down list to choose the format you HAVE, not what you want it to be converted to. So in this case, we choose MDY.
5. We click 'Next' then 'Finish' and VOILA! All sorted!

This method can be used for another common problem, which is to substitute full stops or periods that are used instead of dashes or forward slashes in dates. Excel does not recognise anything other than those two and many accounting packages such as SAP put full stops in the date. Also, some European countries use commas instead of decimal places and using this method can also fix that too.