As a postscript to my grumble about Excel VBA earlier in the week, I spent most of the day on the same problem again.

A number of our users still have Excel 97 on their desktops, and although the fix we put forward worked fine on recent versions of Excel, it doesn’t work on Excel 97. The reason being that on loading under VBA, Excel 97 interprets dates according to the Excel settings, not the VBA settings.

Just to clarify what would happen, if you have a sheet containing dates on a copy of Excel 97 running on a non-US PC and save it as CSV, Excel 97 will convert all the dates to US short date format on save – i.e. mm/dd/yyyy. However when you reload the same file into the same copy of Excel on the same machine it then interprets all the dates using the localised date rules, in our case UK settings – dd/mm/yyyy, and suddenly 1st May becomes 5th Jan and so on.

I tell you, I didn’t know whether to laugh or cry when we found this out, and one of our guys in Scotland had to do it for himself as he didn’t believe it. Suffice to say that we have now written our own CSV export routine…