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.