使用 Javascript 将 Excel 日期序列号转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16229494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting Excel Date Serial Number to Date using Javascript
提问by Hyman
I have the following javascript code that convert date (string) to the Date Serial Number used in Microsoft Excel:
我有以下 javascript 代码将日期（字符串）转换为 Microsoft Excel 中使用的日期序列号：
function JSDateToExcelDate(inDate) {
var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
return returnDateTime.toString().substr(0,5);
}
So, how do I do the reverse? (Meaning that a Javascript code that convert the Date Serial Number used in Microsoft Excel to a date string?
那么，我该如何做相反的事情呢？（意味着将 Microsoft Excel 中使用的日期序列号转换为日期字符串的 Javascript 代码？
回答by silkfire
Try this:
试试这个：
function ExcelDateToJSDate(serial) {
var utc_days = Math.floor(serial - 25569);
var utc_value = utc_days * 86400;
var date_info = new Date(utc_value * 1000);
var fractional_day = serial - Math.floor(serial) + 0.0000001;
var total_seconds = Math.floor(86400 * fractional_day);
var seconds = total_seconds % 60;
total_seconds -= seconds;
var hours = Math.floor(total_seconds / (60 * 60));
var minutes = Math.floor(total_seconds / 60) % 60;
return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}
Custom made for you :)
为您量身定做 :)
回答by Gil
I made a one-liner for you:
我为你做了一个单线：
function ExcelDateToJSDate(date) {
return new Date(Math.round((date - 25569)*86400*1000));
}
回答by leggett
No need to do any math to get it down to one line.
无需做任何数学运算即可将其归结为一行。
// serialDate is whole number of days since Dec 30, 1899
// offsetUTC is -(24 - your timezone offset)
function SerialDateToJSDate(serialDate, offsetUTC) {
return new Date(Date.UTC(0, 0, serialDate, offsetUTC));
}
I'm in PST which is UTC-0700 so I used offsetUTC = -17to get 00:00 as the time (24 - 7 = 17).
我在 PST，它是 UTC-0700，所以我曾经offsetUTC = -17将 00:00 作为时间（24 - 7 = 17）。
This is also useful if you are reading dates out of Google Sheets in serial format. The documentationsuggests that the serial can have a decimal to express part of a day:
如果您以串行格式从 Google 表格中读取日期，这也很有用。文档建议连续剧可以有一个小数来表示一天的一部分：
Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day.For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30st 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year.
指示日期、时间、日期时间和持续时间字段以“序列号”格式输出为双精度值，如 Lotus 1-2-3 所流行的那样。值的整数部分（小数点左边）计算自 1899 年 12 月 30 日以来的天数。小数部分（小数点右边）将时间计算为一天的一小部分。例如，1900 年 1 月 1 日中午是 2.5，2 因为它是 1899 年 12 月 30 日之后的 2 天，0.5 因为中午是半天。1900 年 2 月 1 日下午 3 点将是 33.625。这正确地将 1900 年视为不是闰年。
So, if you want to support a serial number with a decimal, you'd need to separate it out.
因此，如果您想支持带小数的序列号，则需要将其分开。
function SerialDateToJSDate(serialDate) {
var days = Math.floor(serialDate);
var hours = Math.floor((serialDate % 1) * 24);
var minutes = Math.floor((((serialDate % 1) * 24) - hours) * 60)
return new Date(Date.UTC(0, 0, serialDate, hours-17, minutes));
}
回答by Roland Bouman
Specs:
眼镜：
1) https://support.office.com/en-gb/article/date-function-e36c0c8c-4104-49da-ab83-82328b832349
1) https://support.office.com/en-gb/article/date-function-e36c0c8c-4104-49da-ab83-82328b832349
Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
Excel 将日期存储为连续的序列号，以便它们可以用于计算。1900 年 1 月 1 日是序号 1，而 2008 年 1 月 1 日是序号 39448，因为它是 1900 年 1 月 1 日之后的 39,447 天。
2) But also: https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year
2) 还有：https: //support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.
当 Microsoft Multiplan 和 Microsoft Excel 发布时，他们还假设 1900 年是闰年。这种假设允许 Microsoft Multiplan 和 Microsoft Excel 使用与 Lotus 1-2-3 相同的序列日期系统，并提供与 Lotus 1-2-3 的更大兼容性。将 1900 年视为闰年也使用户更容易将工作表从一个程序移动到另一个程序。
3) https://www.ecma-international.org/ecma-262/9.0/index.html#sec-time-values-and-time-range
3) https://www.ecma-international.org/ecma-262/9.0/index.html#sec-time-values-and-time-range
Time is measured in ECMAScript in milliseconds since 01 January, 1970 UTC. In time values leap seconds are ignored. It is assumed that there are exactly 86,400,000 milliseconds per day.
时间在 ECMAScript 中以 UTC 时间 1970 年 1 月 1 日以来的毫秒为单位进行测量。在时间值中，闰秒被忽略。假设每天正好有 86,400,000 毫秒。
4) https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#Unix_timestamp
new Date(value)An integer value representing the number of milliseconds since January 1, 1970, 00:00:00 UTC (the Unix epoch), with leap seconds ignored. Keep in mind that most Unix Timestamp functions are only accurate to the nearest second.
new Date(value)一个整数值，表示自 1970 年 1 月 1 日 00:00:00 UTC（Unix 纪元）以来的毫秒数，忽略闰秒。请记住，大多数 Unix 时间戳函数只能精确到最接近的秒。
Putting it together:
把它放在一起：
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);
}
As one-liner:
作为单线：
function xlSerialToJsDate(xlSerial){
return new Date(-2209075200000 + (xlSerial - (xlSerial < 61 ? 0 : 1)) * 86400000);
}
回答by SteveR
Although I stumbled onto this discussion years after it began, I may have a simpler solution to the original question -- fwiw, here is the way I ended up doing the conversion from Excel "days since 1899-12-30" to the JS Date I needed:
尽管我在讨论开始多年后偶然发现了这个讨论，但我可能对原始问题有一个更简单的解决方案——fwiw，这是我最终将 Excel“自 1899 年 12 月 30 日以来的天数”转换为 JS 日期的方法我需要：
var exdate = 33970; // represents Jan 1, 1993
var e0date = new Date(0); // epoch "zero" date
var offset = e0date.getTimezoneOffset(); // tz offset in min
// calculate Excel xxx days later, with local tz offset
var jsdate = new Date(0, 0, exdate-1, 0, -offset, 0);
jsdate.toJSON() => '1993-01-01T00:00:00.000Z'
Essentially, it just builds a new Date object that is calculated by adding the # of Excel days (1-based), and then adjusting the minutes by the negative local timezone offset.
从本质上讲，它只是构建一个新的 Date 对象，该对象通过添加 Excel 天数（基于 1），然后通过负本地时区偏移调整分钟数来计算。
回答by Salem Megiddo
So, there I was, having the same problem, then some solutions bumped up but started to have troubles with the Locale, Time Zones, etc, but in the end was able to add the precision needed
所以，我在那里，遇到了同样的问题，然后一些解决方案出现了问题，但开始在区域设置、时区等方面遇到问题，但最终能够增加所需的精度
toDate(serialDate, time = false) {
let locale = navigator.language;
let offset = new Date(0).getTimezoneOffset();
let date = new Date(0, 0, serialDate, 0, -offset, 0);
if (time) {
return serialDate.toLocaleTimeString(locale)
}
return serialDate.toLocaleDateString(locale)
}
The function's 'time' argument chooses between displaying the entire date or just the date's time
该函数的“时间”参数在显示整个日期或仅显示日期时间之间进行选择
回答by RaschidRafaelly
It's an old thread but hopefully I can save you the time I used readying around to write this npm package:
这是一个旧线程，但希望我可以为您节省我用来准备编写这个 npm 包的时间：
$ npm installjs-excel-date-convert
$ npm installjs-excel-日期转换
Package Usage:
包装用途：
const toExcelDate = require('js-excel-date-convert').toExcelDate;
const fromExcelDate = require('js-excel-date-convert').fromExcelDate;
const jul = new Date('jul 5 1998');
toExcelDate(jul); // 35981 (1900 date system)
fromExcelDate(35981); // "Sun, 05 Jul 1998 00:00:00 GMT"
You can verify these results with the example at https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
您可以使用https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system上的示例验证这些结果
The Code:
代码：
function fromExcelDate (excelDate, date1904) {
const daysIn4Years = 1461;
const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
const daysFrom1900 = excelDate + (date1904 ? daysIn4Years + 1 : 0);
const daysFrom1970 = daysFrom1900 - daysIn70years;
const secondsFrom1970 = daysFrom1970 * (3600 * 24);
const utc = new Date(secondsFrom1970 * 1000);
return !isNaN(utc) ? utc : null;
}
function toExcelDate (date, date1904) {
if (isNaN(date)) return null;
const daysIn4Years = 1461;
const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
const daysFrom1970 = date.getTime() / 1000 / 3600 / 24;
const daysFrom1900 = daysFrom1970 + daysIn70years;
const daysFrom1904Jan2nd = daysFrom1900 - daysIn4Years - 1;
return Math.round(date1904 ? daysFrom1904Jan2nd : daysFrom1900);
}
If you want to know how this works check: https://bettersolutions.com/excel/dates-times/1904-date-system.htm
如果您想知道这是如何工作的，请检查：https: //bettersolutions.com/excel/dates-times/1904-date-system.htm
回答by catamphetamine
// Parses an Excel Date ("serial") into a
// corresponding javascript Date in UTC+0 timezone.
//
// Doesn't account for leap seconds.
// Therefore is not 100% correct.
// But will do, I guess, since we're
// not doing rocket science here.
//
// https://www.pcworld.com/article/3063622/software/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html
// "If you need to calculate dates in your spreadsheets,
// Excel uses its own unique system, which it calls Serial Numbers".
//
lib.parseExcelDate = function (excelSerialDate) {
// "Excel serial date" is just
// the count of days since `01/01/1900`
// (seems that it may be even fractional).
//
// The count of days elapsed
// since `01/01/1900` (Excel epoch)
// till `01/01/1970` (Unix epoch).
// Accounts for leap years
// (19 of them, yielding 19 extra days).
const daysBeforeUnixEpoch = 70 * 365 + 19;
// An hour, approximately, because a minute
// may be longer than 60 seconds, see "leap seconds".
const hour = 60 * 60 * 1000;
// "In the 1900 system, the serial number 1 represents January 1, 1900, 12:00:00 a.m.
// while the number 0 represents the fictitious date January 0, 1900".
// These extra 12 hours are a hack to make things
// a little bit less weird when rendering parsed dates.
// E.g. if a date `Jan 1st, 2017` gets parsed as
// `Jan 1st, 2017, 00:00 UTC` then when displayed in the US
// it would show up as `Dec 31st, 2016, 19:00 UTC-05` (Austin, Texas).
// That would be weird for a website user.
// Therefore this extra 12-hour padding is added
// to compensate for the most weird cases like this
// (doesn't solve all of them, but most of them).
// And if you ask what about -12/+12 border then
// the answer is people there are already accustomed
// to the weird time behaviour when their neighbours
// may have completely different date than they do.
//
// `Math.round()` rounds all time fractions
// smaller than a millisecond (e.g. nanoseconds)
// but it's unlikely that an Excel serial date
// is gonna contain even seconds.
//
return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour) + 12 * hour);
};
