How To Convert Dates Between Australian & US Formats In Excel

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.

Excel Convert dates from MDY to DMY


    This comment has been deemed inappropriate and has been deleted.

      Methinks you're right! Fixed now.

    I 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).

    Using 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.

    I'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...

    There'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)

    Thanks a lot Kaneda. Your solution worked

Join the discussion!