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.
Hi Helen Bradley!
Thank you, it was very important your information, but I would like to know other easy opcion, because I have several dates so I need to identify them in the same column…
This really is the quickest option because you can select an entire column of dates and to them all in the one step.
Helen
This is a useful tip as usual. Thanks
Note a typographical error on second para “read 22/11/2010 instead of 11/22/2020”.
Thank you.. fixed.
Helen
Never seen a btteer post! ICOCBW
Really very informative and to the point.
Thanks.
Hi Helen, further to your example, I have a report with many cells in US format m/d/yyyy h:mm:ss and need to convert them to AUS format (as my region is Australia) to work out the number of minutes between 2 cells (i.e. a start time and an end time). If I use text to columns, it wipes out the time value in the cell.
You are a lifesaver, thanks!
This was really Helpful; Been always doing it the hard way with formulas . Thanks a ton 🙂
Thanks a lot!! 🙂
Thanks Helen. I’ve been coming across your Office posts in the papers and online and they’re always really helpful.
Graham
Thankyou so much, you saved me so much time with the information I have to analyse. Absolutely wonderful Helen, Thankyou
Thanks Helen. This worked great for changing dates from US to Australian. You saved me alot of time. Cheers, Linda.
Thanks, this worked beautifully. BUT new dates I add to the converted column are not staying in Aussie format when they are sorted if first number is 12 or under, even if the whole column was selected during the process above. Eg: 15-10-12 stays in October, but 5-10-12 is sorted as 10th May. Any tips on how to avoid this, especially when working on a mac?
Thanks,
Caecilia
Helen – you have just solved a problem I’ve been dealing with all year. Same thing – credit card info from two separate cards that show up all wonky, but identified as DMY when they are actually MDY.
THANKS SO MUCH FOR POSTING! This saved me so much time and energy and it was so simple.
Awesome. Thanks!
WOW! Thank you so much!!! Super helpful!
fantastic – works like a charm!
Worked for me. That is, my Canadian data was d/m/y and Excel would not sort it properly until I got it into m/d/y format. Much appreciated.
Thanks Helen,now my spreadsheet with US dates makes half sence.
Yes but Excel still sees these dates in American format. If you try to subtract two dates it treats them both as MDY
That should not be the case.. John. This is supposed to change the dates to a consistent format so that won’t happen. I you like, email me at helen@helenbradley.com and send me a worksheet with the problem so I can check it for you?
Helen
Here is how you can do this with a formula.
In cell A1:
30/09/2013
In cell A2:
=DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE(LEFT(A1,2)))
This converts from AUS in cell A1 (which is a text field) to Excel’s DATE format. Once you have the date in Excel’s DATE format, you can display it as AUS, US, ISO or any format you wish. And, you can also make calculations.
Simply add a column after the date column, add the formula in the top row cell of the new column and copy the formula to all cells in the new column.
Thanks Tord
This is a handy formula to use to break up the date and reassemble it into the ‘correct’ format.
Thank you for posting it.
cheers
Helen
Thanks. You save my life. I have to work with various type of these D-M-Y format and it really consume my time until your advice help me.
Superb explanation! It works.
Hello, I formatted my date to convert to yyyy-mm-dd. When I type in 09-30-2016 (Sept. 30.) it converts to my correct format which 2016-09-30. But when I type it as 30-09-2016 for testing purposes it doesn’t convert to 2016-09-30. Help please!!
Hi,
Thank you so much, for the much needed help in resolving splitting of time & date.
this won’t work if you have the first number larger than 12.
Great tip for date conversion. In Australia our date is always d/m/y and I asked for the file to do that. It still gave me data with m/d/y. I didn’t think to try a conversion based on the date format it showed to trick it to convert to what I wanted. Such a time saver.
Regards
Leigh