I’ve always been annoyed that Excel changes my dates to a particular format and then I have to go back and fix the dates.

Excel’s problem(s) with dates

This came up at a recent STRIPS Data Subteam meeting. Megan O’Donnell mentioned that Excel has (or had) an issue with dates when cross platforms, e.g. Windows and Mac.

Apparently the Windows and Mac version used a different date origin. Thus, if you open a file on a Windows and the same file on a Mac, it will (or previously would) show you different dates.

Apparently this is not the only issue with Excel dates as apparently Excel erroneously treats the year 1900 as a leap year. And, according to this wikipedia article, this bug is now being promoted as a standard for the Ecma Office Open XML (OOXML) specification.

Partial solution

The ISO 8601 standard on dates suggests recording dates in the YYYYMMDD format or, if you prefer, YYYY-MM-DD. The latter is also conveniently recognized as a date by as.Date() in R.

While I don’t have a fix for the previous section, we can at least fix dates as displayed in Excel. Well, I don’t have a fix for the above section, You can force Excel to use your preferred date format which I have been doing for a while.

You can also set the system default in both Windows and Mac. Why haven’t I done this sooner?????

blog comments powered by Disqus


19 December 2018