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.