Changing Excel date format from US to English or Australian

A client had dates that she imported into Excel on a regular basis but she was based in Australia whilst the dates were being imported in US format. This was causing a problem as it was hard to reformat them easily.

Another alternative is to use Text to Columns for this formatting.

Select the data range where the dates are incorrectly formatted. Then on the Data tab, in the Data Tools group, click Text to Columns. Select Fixed Width. Click Next twice.

In Step 3 of the wizard, click Date and from the dropdown, click MDY. Click Finish.

Since this was something she needed to do on a regular basis and then sort the data set so that the most recent date was at the top, we created a simple macro to automate this.

The macro steps were:

Select the column including the dates. Use the Text to Columns as shown above. Click in a single cell within the column that has the dates, click the Z-A Sort button. Simple but effective.

Find out more about our Microsoft Excel training Sydney, Australia.

Find out more about our Microsoft Excel training London, Middlesex and Surrey, UK or our Microsoft Excel training Dorset, Hampshire, Wiltshire, UK

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s