Setting Excel dates to the ISO standard
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
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.
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.
blog comments powered by Disqus