A customer sent through a spreadsheet with what appeared to be dates displayed in column A in the format YYYY-MM-DD. The spreadsheet was the result of a data export from a bespoke system and the “dates” were actually text entries.
The customer wanted the dates to be converted to DD/MM/YYYY format. One way to do this is to use the =DATEVALUE function. However, another way is to use Text to Columns.
Select the cells containing the “dates”
Select Text To Columns. You’ll find this on the DATA tab/menu, depending on which version of Excel you are using.
Ensure that the original data type is set to Delimited and click Next
Ensure that all the checkboxes are clear and click Next
Set the Column Data Format to DMY and select a destination cell. In my example the original data is in column A so I selected C1, that way I can compare the original and the new. Click Finish
You may need to widen column C but what you now have in column C are real dates in the correct format.
UPDATE: 2nd February 2014: here’s a short video: