Today I encountered a problem when downloading some data from an Australian bank. Australia uses the DMY date format whereas the US uses MDY, my computer is set to use US as its region which means Excel expects dates to be entered using MDY format. Excel gets its country information which affects dates, in particular, from the setting you have for your Windows Region.
So, I had a .csv file in which all the dates were entered wrongly – they read 22/11/2010 instead of 11/22/2010, for example. When the dates are typed incorrectly for the country you are currently set to use, you can’t simply apply a format to them to fix the problem because there is no such date as 22/11/2010. And worse still a date like 2/1/2011 which is 2-Jan-2011 in Australia will be formatted as 1-Feb-2011 in my US Excel – throwing all my data spectacularly out.
So what to do?
The simplest solution is to use the Excel data parse tool. Select the column of dates and choose Data > Text To Columns. This opens the Convert Text To Columns Wizard which is the old way of parsing data into Excel.
In Step 1 select either option as you only have one column of data selected anyway. Click Next twice. Now in step three of the wizard, select the Date option and select the formatting for the displayed data. So if the data has been typed in DMY format, select DMY. If it has been typed in MDY format, choose MDY. Click Finish.
The date data will be automatically converted to match the correct date syntax for your version of Excel. In short this converts all my Australian dates to US date format so they are correct in Excel. Select the column of dates and you can now format the dates using your preferred format.
It’s a simple but effective solution that avoids the necessity of retyping the date data.