Just to make the day complete, part of the system we were updating had a bug raised against it by another team. The module in question imports data from excel spreadsheet files that are based on a standard template.
The issue for the other team was that they worked with data that was to be imported in a completely different spreadsheet format. To import their data they had implemented a VBA macro to copy the data from their sheet to another in the template format.
The issue was that the date columns were not importing properly. (They looked right.)
The ODD thing that we found by accident was that if we opened the sheet to look at it, and then closed it, Excel asked if we wanted to save the changes (?). If you save the changes, the sheet imports without issue.
Looking at their VBA, they were setting a numberformat of dd/mm/yyyy on the cell. So the code would look a bit like:
cell.value = CDate(value) cell.NumberFormat="dd/mm/yyyy"
Removing the number format also fixed the issue….