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.
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.
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.
Find out more details about our tailored Microsoft Excel training in Dorset, Wiltshire, Somerset and Hampshire.