Excel Date与JavaScript Date之间的转换

最近用nodejs开发一个Excel导入功能，发现Excel的日期类型转换出来有的正确，有的差若干天？查阅的若干资料，并代码验证，总结一下。

Excel Date

关于Excel Date类型的官方描述参考一下链接：
https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

Excel Date to JavaScript Date

Stackoverflow上有人讨论，具体参见：https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript。

一段有参考价值的代码：

function xlSerialToJsDate(xlSerial){
  // milliseconds since 1899-31-12T00:00:00Z, corresponds to xl serial 0.
  var xlSerialOffset = -2209075200000; 

  var elapsedDays;
  // each serial up to 60 corresponds to a valid calendar date.
  // serial 60 is 1900-02-29. This date does not exist on the calendar.
  // we choose to interpret serial 60 (as well as 61) both as 1900-03-01
  // so, if the serial is 61 or over, we have to subtract 1.
  if (xlSerial < 61) {
    elapsedDays = xlSerial;
  }
  else {
    elapsedDays = xlSerial - 1;
  }

  // javascript dates ignore leap seconds
  // each day corresponds to a fixed number of milliseconds:
  // 24 hrs * 60 mins * 60 s * 1000 ms
  var millisPerDay = 86400000;

  var jsTimestamp = xlSerialOffset + elapsedDays * millisPerDay;
  return new Date(jsTimestamp);
}

本人综合几家之长，写了一个ExcelDateHelper类。

class ExcelDateHelper {
  static toJSDate(serial) {
    let d = new Date(-2209075200000 + (serial - (serial < 61 ? 0 : 1)) * 86400000);
    return d;
  }
  static toExcelDate(date) {
    let d = date.getTime() / 86400000 + 25569;
    return d < 61 ? d - 1 : d;
  }
}
export default ExcelDateHelper;

测试代码：

let now = new Date();
let timezoneOffset = now.getTimezoneOffset();
console.log('===>', now, timezoneOffset);
let s = ExcelDateHelper.toExcelDate(now);
let arr = [0, 1, 39448, 40729, s];
arr.forEach((d) => {
  console.log(d, ExcelDateHelper.toJSDate(d), ExcelDateHelper.toExcelDate(DateFormat.toJSDate(d)));
});

输出结果：

===> 2021-11-17T14:29:57.149Z -480
0 1899-12-31T00:00:00.000Z 0
1 1900-01-01T00:00:00.000Z 1
39448 2008-01-01T00:00:00.000Z 39448
40729 2011-07-05T00:00:00.000Z 40729
44517.60413366898 2021-11-17T14:29:57.149Z 44517.60413366898