Convert Date (dd/mm/yyyy) to Excel Serial Number in JavaScript

Question

Description:

I am generating an Excel file using the XLSX on an Angular Project (npm install xlsx), but I am facing a problem showing a precise date. If I try to use JavaScript date object it somewhow loses precision and show a wrong date. It also fails if I try to use a string corresponding to 'dd/mm/yyyy'.

I figured out that to make the Microsoft Excel have a precise date with the format dd/mm/yyyy, you need to work with Excel Serial Format, that follows a pattern.

Here is some examples of this format:

Date: 01/01/1900 - Serial: 1
Date: 02/01/1900 - Serial: 2
Date: 09/04/1900 - Serial: 100
Date: 26/09/1902 - Serial: 1000
Date: 08/09/1913 - Serial: 5000
Date: 18/05/1927 - Serial: 10000
Date: 24/07/1998 - Serial: 36000

*You can try that on Microsoft Excel to check for more examples.

Above here is a prototype of the function I need, that will recevei a date (dd/mm/yyyy) and will return it's serial number equivalent.

private JSDateToExcelDate(inDate) {
   if(inDate != null) { 
   //TO DO
   }
}

Additional information:

The xlsx framework has can modify any cell telling it that value type will be Date. With that in mind all I need is a Date in Serial Number format.

UPDATE 11/06/2019 - 20:14

I have found a solution with the following function that provides the number I want. I solved it partialy because the framework XLSX might be changing the value, because I am getting an output as "31/12/1969 21:00:16", witch I found out that is a default output related due to some invalid date. (I have created the function on a Angular Project, so it might contains specific syntax)

private JSDateToExcelDate(date) {
    var temp: any;
    temp = new Date(date);
    if(date != "NaN" && date != "Invalid Date") {
      var days = Math.round((temp.setHours(0,0,0,0) - <any>new Date(1899, 11, 30)) / 8.64e7);
      let ret:any = (days).toFixed(10);
      if(!isNaN(ret)) {
        return ret;  
      }
    }
    return '';
  }

https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript — Rafael Lucini, Jun 11 '19 at 20:13
@RafaelLucini I also have tried that function over that link. It does not work — Marcus V. B. Siqueira, Jun 11 '19 at 20:23
@Screll—no. The decimal part is always positive, but the integer number part is negative before 1899-12-30. So 6am on 29 Dec 1899 is -1.25 (i.e. minus 1 day, plus 0.25 days), not -0.75. ;-) — RobG, Jun 11 '19 at 22:56
I have worked on the duplicated link the was placed and was able to built a function that returns the integer Serial Number I need, but the framework XLSX might be changin the value, because it places a default date value over the excel, but if I console.log(MyDate), I will see it's Serial Number correct, I can even pick it and paste on excel Date Field and see the correct date. — Marcus V. B. Siqueira, Jun 11 '19 at 23:16

score 1 · Answer 1 · answered Jun 11 '19 at 20:15

1

var start = new Date('1900-01-01')
return (inDate - start)/(1000 * 60 * 60 * 24);

answered Jun 11 '19 at 20:15

user10474426

29
1
1
6

Timezone? Or maybe `floor` it? – Qwertiy Jun 11 '19 at 20:18
2

The epoch is 1899-12-30 00:00:00, and the decimal parts of the value are separate from the integer day part of the value. That is simple for dates after the epoch, but means the two parts must be calculated separately before it. – RobG Jun 11 '19 at 20:26

Convert Date (dd/mm/yyyy) to Excel Serial Number in JavaScript

1 Answers1