Why does Excel not recognise and format dates consistently?

Question

I'm using data connections in excel to link to data exported from another software package. The analysis I do relies on dates captured by the software and it is very helpful to group by the dates and doing other operations on it.

The spreadsheet I created last week worked perfectly and Excel handled the dates as it should. When I updated the connection this week, all the groupings in my pivot tables are messed up because Excel just treats the date string as text. I have not changed anything in the spreadsheet apart from updating the connection.

The date column in the exported spreadsheet is automatically formatted correctly. Nothing major has changed in the past week except for a few extra lines of data. The captured data is always in the same format and there is no text accidentally being written to the date column.

The data spans a range of over 3 years and any day/month ambiguity has been handled correctly before without issues and without me having to give attention to it, so I don't suspect that this is the reason.

I have tried to select the cell and specify the formatting but it does not have any effect. The text formatting is also exactly as my regional settings so the error does not lie there.

Even when I reference one of those date cells in a formula it still outputs as text. When I enter and exit edit mode in one of the cells, then suddenly Excel knows what to do with the text and formats it correctly.

I am using Excel 2013, but I've encountered the same phenomenon numerous times in 2010 and with other formats such as numbers as well being treated as text.

Will you please explain to me why Excel does this and what I can do to rectify this? My main concern is why Excel is so inconsistent in how it treats my data when the data is clearly consistent.

Please note that I do not wish to copy the column and paste it with some transform and there's no way I'm editing 20000+ lines by hand. The purpose of the spreadsheet is for other people to use and to be updated regularly with as little effort and explanation as possible.

Solution 1

I feel quite bad answering my own question but I managed to drill down to the source of the problem which would have been hard for someone who doesn't have the spreadsheets or someone must have experienced exactly the same before.

I suspect that when I export the data from the software package, the dates are written as text. When I open the exported sheet then Excel does what it does with dates and converts it to the correct format. When I save, even if I had not made ant changes, the data comes through correctly from the connection.

This is quite strange, but it seems to be the problem.

Solution 2

best way to solve this is to save the file in a text format and then "import" the file in an empty sheet using the "from Text" import under Data - Get External Data. When importing from texts, make sure to specify the correct date format (D/M/Y or M/Y/D or any other combination) on the columns or raws including dates

Comments

[Andrew Spencer]

I think answering your own question is actually encouraged, especially if it's the right answer!