I download a lot of data form various sources, so the quality and formatting of it can vary significantly.  One issue that occurs a lot of the time is a date field being formatted as text.  It usually is displayed something like this 20150824.

It looks nice and neat but if we try to use it for analysis it is not very co-operative or user friendly.  We cannot apply any real date formatting like Short Date which is what I want in this example, as Excel actually sees the cell contents as text.

We can use the built in Excel Text To Columns tool to reformat this data  (I find is the quickest way to resolve this).  Let’s take a look at an example.  See my data extract below from one of my data sources.  Let’s change the text to short date.

 

• Select the cell range with the data
• Data Tab – Data Tools – Text To Columns
• The Convert Text To Columns Data Wizard Step 1 of 3 appears
• Select Delimited then Next

 

• Hit Next then Next again
• In Step 3 of the Wizard
• Select Date and YMD form the drop down box

 

• Select the destination cells in my example I want the date range to start in cell D3
• Hit Finish
• See the result below- Job Done!