We’ve been doing some pretty large imports of excel data into JavaScript recently and I just came across an interesting issue.
We’ve been using the XLSX package to import the spreadsheets, before processing them for upload to our GraphQL API. I noticed the dates on the server were coming out incorrectly and traced it back. The dates were wrong as soon as they came out of the XLSX package.
We were using the cellDates option to get JS dates directly from the import, but, due to daylight savings time, they were coming out an hour earlier than expected. This resulted in 2015-01-01 00:00 becoming 2014-12-31 23:00! Nightmare!
So I did some digging into dates in excel to find out what was going on and learnt a whole bunch of crazy stuff.
- Excel does not recognise time zones.
- Excel (using the default 1900 Date System) cannot display dates before the year 1900
- The 1900 Date System incorrectly accepts February 29, 1900, however 1900 was not a leap year.
- Excel works this way because there was a bug in Lotus 123, which had nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus’ bug in order to be compatible.
- Excel stores dates as a number representing the number of days since 1900-Jan-0.
- Excel can also treat the date as the number of days since 1904-Jan-0.
- 1904-mode or 1904-system, is used for compatibility with the Macs, which did not recognise dates before Jan 1904.
It turns out none of these were actually the problem, but they were fascinating.
The solution
Digging in to the import to find out why they were parsing the dates wrong was a step too far. It looks like it was importing the date as BST, then converting to GMT which set it back an hour.
We switched back to using the numerical system of date parsing (more like a UNIX timestamp) and used excel-date-to-js which just calls everything GMT so we don’t have to worry about offsets.
Fixed!