Converting Dates from mm/dd/yyyy to dd/mm/yyyy

By Jeff Robson

Sometimes you get a spreadsheet or text file that has US format dates i.e. mm/dd/yyyy

As our UK/Australian date format is dd/mm/yyyy, you end up with some dates that Excel recognises (e.g. where the day of the month is 12 or less) and some that it doesn’t (i.e. all other dates).

Even if Excel has recognised the dates, the day and month are mixed up (i.e. 4/2/2013 is recognised as the 4th of Feb instead of the 2nd of April).

You can tell that things are mixed up because some dates are left aligned (meaning that Excel thinks they’re text), while others are right-aligned (i.e. recognised as date numbers).

What to do?

You could manually re-type them all if you only have a few dates but if you have thousands, this isn’t really the most exciting way to spend your day!

Instead of typing until your fingers fall off, take a look at the attached workbook file!

Bonus tip: Converting dates to uppercase

The workbook file also includes an example of displaying a date such as 15-March-2013 in uppercase “15-MARCH-2013”.