1

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.

Max888
  • 3,089
  • 24
  • 55
  • 1
    Is there any logic for it like 43527 should be equal to given date? – Th3 May 19 '20 at 16:49
  • 1
    A javascript date is based on Jan 1 1970 and an Excel date is based on Jan 1 1900. More info about Excel date storage [here](https://support.office.com/en-us/article/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252) – James May 19 '20 at 16:51
  • Actually, 43527 corresponds to '03-Mar-2019' – Yevhen Horbunkov May 19 '20 at 17:00
  • @James—the JS epoch is 0, but the Excel ([OADate](http://msdn.microsoft.com/en-us/library/system.datetime.tooadate.aspx)) 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](https://stackoverflow.com/a/15550284/257182). – RobG May 19 '20 at 22:17

1 Answers1

1

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

Prete
  • 97
  • 1
  • 5
  • Indeed not needed, thanks. – Prete May 19 '20 at 17:08
  • `*86400*1000` may be shortened to `*864e5` – Yevhen Horbunkov May 19 '20 at 17:12
  • 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. – RobG May 19 '20 at 22:12