I have a .xlsx file which has a table which contains dates. When I read the table in to javascript (with xlsx nodejs package) the dates are formatted as numbers. I would like to convert these numbers to dates. For example the number 43527 should be the date 13/03/2019. I have tried new Date(43527*60*60*24*1000) but this gives the wrong answer of 2089-03-04T00:00:00.000Z.
-
1Is there any logic for it like 43527 should be equal to given date?Th3– Th32020-05-19 16:49:47 +00:00Commented May 19, 2020 at 16:49
-
1A javascript date is based on Jan 1 1970 and an Excel date is based on Jan 1 1900. More info about Excel date storage hereJames– James2020-05-19 16:51:56 +00:00Commented May 19, 2020 at 16:51
-
Actually, 43527 corresponds to '03-Mar-2019'Yevhen Horbunkov– Yevhen Horbunkov2020-05-19 17:00:36 +00:00Commented May 19, 2020 at 17:00
-
@James—the JS epoch is 0, but the Excel (OADate) epoch day is 1, which, confusingly, makes 1 Jan 1900 day 2. In ECMAScript terms, the equivalent OADate epoch is 30 Dec 1899. Try it here.RobG– RobG2020-05-19 22:17:21 +00:00Commented May 19, 2020 at 22:17
Add a comment
|
1 Answer
Excel stores dates internally using OLE Automation Date (OADate).
The base OLE Automation Date is midnight, 30 December 1899. OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. [https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1#remarks]
You need to subtract 25,569, which is the number of days between 30 December 1899 and 1 January 1970, before converting to milliseconds.
To convert the date:
var excelDate = 43527;
var parsedDate = new Date((excelDate - 25569) * 86400 * 1000)).toISOString();
As mentioned here Converting Excel Date Serial Number to Date using Javascript
3 Comments
Prete
Indeed not needed, thanks.
Yevhen Horbunkov
*86400*1000 may be shortened to *864e5
RobG
If you've found a duplicate answer, you should mark this question as a duplicate, not copy or rewrite an answer from the duplicate. If you have a better answer, post it at the duplicate. Excel uses OADate, the epoch is 30 Dec 1899, not 1 Jan 1900.
