If you’ve got a bunch of dates in US mm/dd/yyyy format and need to convert them into Australia’s dd/mm/yyyy format (or vice-versa), it can be hard to know where to start. Office expert Helen Bradley details how you can work through that process in Excel.As Bradley notes, you can’t simply apply a different date format to the data as this will either produce errors or convert dates into the wrong month (3/1/2011 is January 3 in Australia, but March 1 in the US). However, using Excel’s Text to columns feature, you can reprocess the data to render correctly. Hit the post for the full details.
poedgirl
Monday, March 21, 2011 at 1:04 PMConvert between mm/dd/yyyy and mm/dd/yyyy? Me thinks this article needs some proof reading.
Angus Kidman
Monday, March 21, 2011 at 1:12 PMMethinks you’re right! Fixed now.
Cameron
Monday, March 21, 2011 at 3:09 PMI always insist on using the ISO date format of YYYYMMDD to avoid any month/day ambiguity. This also allows for easy sorting as the dates will be in numerical order (if parsed as numbers of course).
Chris
Monday, March 21, 2011 at 4:15 PMUsing Excel 2003, I’ve always got around the problem by selecting Format->Cells->Custom, then entering the desired format, be it dd/mm/yy or mm/dd/yy. This gets around the problem of the desired format not being available using Format->Cells->Date.
ken
Monday, March 21, 2011 at 8:32 PMI’m with Cameron and try to use ISO dates everywhere. Time to totally get rid on the dmy/mdy confusion, and ISO dates sort correctly whether they’re numeric or text. It’s interesting noticing the number of major commercial websites that use ISO dates in their folder structures. How sensible…
Kaneda
Thursday, September 15, 2011 at 1:46 AMThere’s even a easy way, Just change you region setting > date format, in control panel. Then save the csv file. The good thing is it updates all dates in the document.(Excel 2007)
Poon
Friday, April 27, 2012 at 9:55 AMThanks a lot Kaneda. Your solution worked