Wrong day number for dates before March 1 1900. #2942

New Issue

juanii · 2023-05-12T18:52:51Z

juanii commented

2023-05-12 18:52:51 +00:00

Excel (inconrrectly) considers 1900 to be a leap year (https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year). datenum function is blindly adding an extra day to account for Feb 29 1900 to all dates, making the result wrong for dates earlier than Mar 1 1900. For example, calling the datenum function with the date Jan 1 1900 returns 2 instead of 1 (from https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system, "When you enter a date, the date is converted into a serial number that represents the number of elapsed days starting with 1 for January 1, 1900.").

Excel (inconrrectly) considers 1900 to be a leap year (https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year). datenum function is blindly adding an extra day to account for Feb 29 1900 to all dates, making the result wrong for dates *earlier* than Mar 1 1900. For example, calling the datenum function with the date Jan 1 1900 returns 2 instead of 1 (from https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system, "When you enter a date, the date is converted into a serial number that represents the number of elapsed days starting with 1 for January 1, 1900.").

sheetjs commented

2023-05-12 19:17:23 +00:00

There are corrections for the 1900 bug throughout the library, from the parsing and writing codecs to the SSF number formatting library. If you can share a problematic file or a situation where the date is off by a day, we can take a closer look and reopen the issue.

sheetjs closed this issue

2023-05-12 19:17:23 +00:00

juanii commented

2023-05-12 19:45:49 +00:00

Actually I fixed this by copying the same date treatment used in datenum_local, along with another bug (#2644) but I'm having trouble pushing the commits to either a branch or fork of my own to do the PR.

sheetjs commented

2023-05-12 19:47:53 +00:00

The referenced bug is related to a V8 bug (https://bugs.chromium.org/p/v8/issues/detail?id=7863) that we plan on addressing in 0.20

juanii commented

2023-05-12 20:59:43 +00:00

Well, I found that it can be solved idependently of the V8 bug.

If I understood correctly, the error is introduced because the basedate time zone offset is added to dnthresh (through the getTime() function) with seconds resolution and then subtracted (through the getTimezoneOffset() function) with minutes resolution.

But there's a catch: the basedate is only needed to calculate the day number (integer part of the cell value) but not the time of day (the fractional part of the cell value).

What I did is separate the calculation of the day number from the calculation of the time of day, so the basedate time zone offset isn't used at all. Of course it involves some more math, but the result is correct.

Well, I found that it can be solved idependently of the V8 bug. If I understood correctly, the error is introduced because the basedate time zone offset is added to dnthresh (through the getTime() function) with seconds resolution and then subtracted (through the getTimezoneOffset() function) with minutes resolution. But there's a catch: the basedate is only needed to calculate the day number (integer part of the cell value) but not the time of day (the fractional part of the cell value). What I did is separate the calculation of the day number from the calculation of the time of day, so the basedate time zone offset isn't used at all. Of course it involves some more math, but the result is correct.

sheetjs commented

2023-05-13 05:36:13 +00:00

I'm having trouble pushing the commits to either a branch or fork of my own to do the PR.

What is the error message?

> I'm having trouble pushing the commits to either a branch or fork of my own to do the PR. What is the error message?

juanii commented

2023-05-13 14:40:58 +00:00

Don't worry, the error was me trying to do things I don't know about :) Like me coding in JavaScript, me writing in english or me trying to create a PR (which I hope to create in a few minutes).

I owed you the MWE you asked for in the second message. Opening the resulting file with Excel I see the "good" date as May 12 2023 (which is wrong but not the point of this bug report, it's because of #2644) and the "bad" date as Jan 2 1900 which is what this report is about. The "edge 1" and "edge 2" dates are just around Feb 29 1900 which is the turning point from wrong to right dates. Thus, all dates between Jan 1 1900 and Feb 28 1900 have an extra day added.

<html>
<body>
<script src="https://cdn.sheetjs.com/xlsx-0.19.3/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  const rows = [
    { name: "good", value: new Date(2023, 4, 13, 0, 0, 0) },
    { name: "bad", value: new Date(1900, 0, 1, 0, 0, 0) },
    { name: "edge 1", value: new Date(1900, 1, 28, 0, 0, 0) },
    { name: "edge 2", value: new Date(1900, 2, 1, 0, 0, 0) },
  ];
  const worksheet = XLSX.utils.json_to_sheet(rows);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");
  XLSX.writeFile(workbook, "Dates.xlsx", { compression: true });
})();
</script>
</body>
<html>

Regards,
juanii

Don't worry, the error was me trying to do things I don't know about :) Like me coding in JavaScript, me writing in english or me trying to create a PR (which I hope to create in a few minutes). I owed you the MWE you asked for in the second message. Opening the resulting file with Excel I see the "good" date as May 12 2023 (which is wrong but not the point of this bug report, it's because of #2644) and the "bad" date as Jan 2 1900 which is what this report is about. The "edge 1" and "edge 2" dates are just around Feb 29 1900 which is the turning point from wrong to right dates. Thus, all dates between Jan 1 1900 and Feb 28 1900 have an extra day added. ```html <html> <body> <script src="https://cdn.sheetjs.com/xlsx-0.19.3/package/dist/xlsx.full.min.js"></script> <script> (async() => { const rows = [ { name: "good", value: new Date(2023, 4, 13, 0, 0, 0) }, { name: "bad", value: new Date(1900, 0, 1, 0, 0, 0) }, { name: "edge 1", value: new Date(1900, 1, 28, 0, 0, 0) }, { name: "edge 2", value: new Date(1900, 2, 1, 0, 0, 0) }, ]; const worksheet = XLSX.utils.json_to_sheet(rows); const workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, "Dates"); XLSX.writeFile(workbook, "Dates.xlsx", { compression: true }); })(); </script> </body> <html> ``` Regards, juanii

juanii referenced a pull request that will close this issue

2023-05-13 14:43:23 +00:00

Fixes #2942. #2943

juanii reopened this issue

2023-05-14 00:22:22 +00:00

Sign in to join this conversation.