Sheetjs: reading with option `cellDates: true` has wrong date values if original cell date was before 26th march 2018

[pluederitz]

if cells with date value have an date before 03/25/2018 and loading xlsx file with option cellDates: true the loaded cells in worksheet are one day earlier than original date value.

it('with option cellDates: true XLSX should parse date correctly', function() {
var wb = X.read(
fs.readFileSync('./test_files-bug-report/check-date-bug.xlsx'),
{cellDates: true, type: TYPE}
);
var ws = wb.Sheets['check-date-bug'];
assert(dateToString(ws.B46.v) === '2018-6-5'); // work
assert(dateToString(ws.B37.v) === '2018-3-27'); // work
assert(dateToString(ws.B36.v) === '2018-3-26'); // work
assert(dateToString(ws.B35.v) === '2018-3-25'); // fail: '2018-3-24'
assert(dateToString(ws.B34.v) === '2018-3-24'); // fail: '2018-3-23'
assert(dateToString(ws.B3.v) === '2015-1-1'); // fail: '2014-12-31'
assert(dateToString(ws.B2.v) === '2014-1-1'); // fail: '2013-12-31'
});

There is a fork with added test that currently fail:
fork-branch: https://github.com/pluederitz/js-xlsx/tree/bug-date-parsing-cell-dates-true
added test: https://github.com/pluederitz/js-xlsx/blob/bug-date-parsing-cell-dates-true/test.js#L384-L410
added xlsx file: https://github.com/pluederitz/js-xlsx/blob/bug-date-parsing-cell-dates-true/test_files-bug-report/check-date-bug.xlsx

to check bug run added test:

git clone https://github.com/pluederitz/js-xlsx.git
checkout bug-date-parsing-cell-dates-true
npm install
make ctestserv
open browser on http://127.0.0.1:8000

2nd suite "bug-reports" contains test "with option cellDates: true XLSX should parse date correctly (also date before 26th of march 2018)"

added bug test also fail on running tests under node.js

npm test

[bean5]

Do you know whether this bug affects CSV?

[MShake]

Same bug here with the 28th of march.
No matter the years. 28th of march display 27th of march and 29th of march display the 29th.

[ctilley83]

Can't believe this is still open 5 months later. Anyone found a workaround?

[g1r0]

Workaround: use SSF library to convert raw excel number dates.

let workbook = XLSX.read(data, { type, cellDates: **false** });
const ws = workbook.Sheets[workbook.SheetNames[0]];
const dateMode = workbook.Workbook.WBProps.date1904;
let val = ws.B3.v;
XLSX.SSF.format('YYYY-MM-DD', val, { date1904: dateMode }));

[bean5]

Thank you for the workaround. Can it before applied generally or do you
have to apply to only cells that you know are datetimes?

[g1r0]

Thank you for the workaround. Can it before applied generally or do you have to apply to only cells that you know are datetimes?

Probably you may try to use z property of the Cell Object to identify the cell stores a date number.

[maihannijat]

This fixed for me:
https://github.com/SheetJS/js-xlsx/issues/1293

[thomasnordlund]

This is a deal breaker. It's happening on all dates, even more recent than March 26th 2018. Has this not been fixed yet?

[Coada]

I had the same issue, and ended up parsing it on my own:

function excelDateToISODateString(excelDateNumber) {
    return new Date(Math.round((excelDateNumber - 25569) * 86400 * 1000)).toISOString().substring(0, 10);
}

This is based on a Stackoverflow answer: https://stackoverflow.com/a/22352911/11599033

[fractAlbert]

I was able to resolve it by keeping cellDates: true and then parsing the date into an array using
var data = XLSX.utils.sheet_to_json(ws, { header: 1, raw: true, sheetStubs: true });

Then, for a given cell in the array with this code, where c is the column and r is the row of the data in the array.

Basically if the value is a date, then I get w from the cell reference to get a formatted date.

        for (r = 0; r < data.length; r++) {
            for (c = 0; c < data[r].length; c++) {
                var cellValue = data[r][c]
                if (cellValue instanceof Date) {
                    var cellRef = XLSX.utils.encode_cell({ c: c, r: r+1 });
                    cellValue = ws[cellRef].w
                }