Microsoft Excel – changing dates in the form 1. Sep 2018 to a usable date

Sometimes when data comes from another source, the dates are not formatted as dates and there is no quick fix by selecting Short Date as the format from the Number group on the Home tab.

A client I visited this week had dates in the following form and therefore couldn’t easily use them in a PivotTable.

Difficult dates

To convert data like this, first select the column containing the data.

On the Data tab, in the Data Tools group, click Text to Columns. Select Delimited, then click Next. Click Next again.

In the top part, select Date and from the dropdown, select DMY, then click Finish.

Dates conversion

Go to the Home tab and in the Number group, click on the dropdown, where it says General and select Short Date. The data will be formatted appropriately.

converted dates

Find out more details about our tailored Microsoft Excel training in Dorset, Wiltshire, Somerset and Hampshire.

Leave a Reply

Your email address will not be published. Required fields are marked *