Dates and Times日期和时间
File Format Support文件格式支持 (click to show)
Dates are a core concept in nearly every spreadsheet application in existence. Some legacy spreadsheet apps only supported dates. Others supported times as a distinct concept from dates.日期是几乎所有电子表格应用程序的核心概念。一些旧版电子表格应用程序仅支持日期。其他人则支持时间是一个与日期不同的概念。
Some file formats store dates in a textual format, while others store dates with numbers representing a difference from an epoch.一些文件格式以文本格式存储日期，而另一些文件格式则使用表示与历元不同的数字存储日期。
Many spreadsheet apps use special number formats to signal that values are dates or times. Quattro Pro for DOS had a distinct set of Date number formats and Time number formats, but did not have a mixed Date + Time format. OpenOffice uses ISO 8601 duration strings for pure time data.许多电子表格应用程序使用特殊的数字格式来表示值是日期或时间。用于DOS的Quattro Pro有一组不同的日期数字格式和时间数字格式，但没有混合的日期+时间格式。OpenOffice将ISO 8601持续时间字符串用于纯时间数据。
Lotus 1-2-3 used a "1900" date system, while Numbers exclusively supports 1904 under the hood. Excel file formats typically include options for specifying the date system. OpenOffice can support arbitrary starting dates.Lotus1-2-3使用了“1900”日期系统，而Numbers仅在后台支持1904。Excel文件格式通常包括用于指定日期系统的选项。OpenOffice可以支持任意的开始日期。
| Formats | Date | Time | D+T | Date Storage | Date System |
|---|---|---|---|---|---|
| NUMBERS | ✔ | ✔ | ✔ | Number | 1904 Only |
| XLSX / XLSM | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLSX (Strict ISO) | ✔ | ✔ | ✔ | Relative Date | 1900 + 1904 |
| XLSB | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLML | ✔ | ✔ | ✔ | Relative Date | 1900 + 1904 |
| XLS (BIFF5/8) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLS (BIFF2/3/4) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLR (Works) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| ET (WPS 电子表格) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| ODS / FODS / UOS | ✔ | ✔ | ✔ | ISO Duration or Date | Arbitrary |
| HTML | ✔ | ✔ | ✔ | Plaintext | Calendar |
| CSV / TSV / Text | ✔ | ✔ | ✔ | Plaintext | Calendar |
| DBF | ✔ | * | * | Number or Plaintext | Calendar |
| DIF | ✔ | ✔ | ✔ | Plaintext | Calendar |
| WK1 | ✔ | ✔ | ✕ | Number | 1900 |
| WKS (Works) | ✔ | ✔ | ✕ | Number | 1900 |
| WQ1 | ✔ | ✕ | Number | 1900 | |
| QPW | ✔ | ✔ | * | Number | 1900 |
X (✕) marks features that are not supported by the file formats. For example, the WK1 file format had date-only formats and time-only formats but no mixed date-time formats.标记文件格式不支持的功能。例如，WK1文件格式有纯日期格式和纯时间格式，但没有混合的日期-时间格式。
Newer DBF levels support a special 较新的DBF级别支持表示日期+时间的特殊T field type that represents date + time.T字段类型。
The QPW file format supports mixed date + time formats in custom number formats.QPW文件格式支持自定义数字格式中的混合日期+时间格式。
Lotus 1-2-3, Excel, and other spreadsheet software do not have a true concept of date or time. Instead, dates and times are stored as offsets from an epoch. The magic behind date interpretations is hidden in functions or number formats.Lotus 1-2-3、Excel和其他电子表格软件没有真正的日期或时间概念。相反，日期和时间存储为与历元的偏移量。日期解释背后的魔力隐藏在函数或数字格式中。
SheetJS attempts to create a friendly JS date experience while also exposing options to use the traditional date codes.SheetJS试图创建一个友好的JS日期体验，同时也公开了使用传统日期代码的选项。
The following example exports the current time to XLSX spreadsheet. The time shown on this page will be the time displayed in Excel以下示例将当前时间导出到XLSX电子表格。此页面上显示的时间将是Excel中显示的时间
How Spreadsheets Understand Time电子表格如何理解时间
Excel stores dates as numbers. When displaying dates, the format code should include special date and time tokens like Excel将日期存储为数字。显示日期时，格式代码应包括特殊的日期和时间标记，如yyyy for long year. EDATE and other date functions operate on and return date numbers.yyyy表示长年份。EDATE和其他日期功能对日期编号进行操作并返回日期编号。
For date formats like 对于yyyy-mm-dd, the integer part represents the number of days from a starting epoch. yyyy-mm-dd等日期格式，整数部分表示从开始历元算起的天数。For example, the date 例如，日期19-Feb-17 is stored as the number 42785 with a number format of d-mmm-yy.19-Feb-17被存储为数字42785，数字格式为d-mmm-yy。
The fractional part of the date code serves as the time marker. Excel assumes each day has exactly 86400 seconds. For example, the date code 日期代码的小数部分用作时间标记。Excel假设每天正好有86400秒。例如，日期代码0.25具有对应于上午6:00的时间分量。0.25 has a time component corresponding to 6:00 AM.
For absolute time formats like 对于像[hh]:mm, the integer part represents a whole number of 24-hour (or 1440 minute) intervals. [hh]:mm这样的绝对时间格式，整数部分表示24小时（或1440分钟）间隔的整数。The value 格式为1.5 in the format [hh]:mm is interpreted as 36 hours 0 minutes.[hh]:mm的值1.5被解释为36小时0分钟。
Date and Time Number Formats日期和时间数字格式
Assuming a cell has a formatted date, re-formatting as "General" will reveal the underlying value. 假设一个单元格有一个格式化的日期，重新格式化为“常规”将显示基本值。Alternatively, the 或者，TEXT function can be used to return the date code.TEXT函数可用于返回日期代码。
The following table covers some common formats:下表涵盖了一些常见的格式：
Common Date-Time Formats通用日期时间格式 (click to show)
yy | Short (2-digit) year |
yyyy | Long (4-digit) year |
m | Short (1-digit) month |
mm | Long (2-digit) month |
mmm | Short (3-letter) month name |
mmmm | Full month name |
mmmmm | First letter of month name |
d | Short (1-digit) day of month |
dd | Long (2-digit) day of month |
ddd | Short (3-letter) day of week |
dddd | Full day of week |
h | Short (1-digit) hours |
hh | Long (2-digit) hours |
m | Short (1-digit) minutes |
mm | Long (2-digit) minutes |
s | Short (1-digit) seconds |
ss | Long (2-digit) seconds |
A/P | Meridiem ("A" or "P") |
AM/PM | Meridiem ("AM" or "PM") |
m and mm are context-dependent. It is interpreted as "minutes" when the previous or next date token represents a time (hours or seconds):m和mm是上下文相关的。当上一个或下一个日期标记表示时间（小时或秒）时，它被解释为“分钟”：
yyyy-mm-dd hh:mm:ss
^^ ^^
month minutes
mmm, mmmm, and mmmmm always represent months.mmm、mmmm和mmmmm始终表示月份。
1904 and 1900 Date Systems1904和1900日期系统
The interpretation of date codes requires a shared understanding of date code 对日期代码的解释需要对日期代码0, otherwise known as the "epoch". Excel supports two epochs:0（也称为“epoch”）有一个共同的理解。Excel支持两个时期：
-
The default epoch is "January 0 1900". The默认历元为“1900年1月0日”。0value is 00:00 on December 31 of the year 1899, but it is formatted as January 0 1900.0值为1899年12月31日的00:00，但其格式为1900年1月0日。 -
Enabling "1904 Date System" sets the default epoch to "January 1 1904".启用“1904日期系统”会将默认历元设置为“1904年1月1日”。The0value is 00:00 on January 1 of the year 1904.0的值是1904年1月1日的00:00。
The workbook's epoch can be determined by examining the workbook's 可以通过检查工作簿的wb.Workbook.WBProps.date1904 property:wb.Workbook.WBProps.date1904属性来确定工作簿的epoch：
if(!(wb?.Workbook?.WBProps?.date1904)) {
/* uses 1904 date system */
} else {
/* uses 1900 date system */
}
1900 was not a leap year. For the Gregorian calendar, the general rules are:1900年不是闰年。对于公历，一般规则是：
every multiple of 400 is a leap year每400的倍数是闰年every multiple of 100 that is not a multiple of 400 is not a leap year不是400的倍数的每一个100的倍数都不是闰年every multiple of 4 that is not a multiple of 100 is a leap year不是100的倍数的每4的倍数都是闰年all other years are not leap years.其他年份都不是闰年。
Lotus 1-2-3 erroneously treated 1900 as a leap year. This can be verified with the Lotus 1-2-3错误地将1900年视为闰年。这可以通过@date function:@date函数进行验证：
@date(0,2,28) -> 59 // Lotus accepts 2/28/1900
@date(0,2,29) -> 60 // <--2/29/1900 was not a real date
@date(0.2,30) -> ERR // Lotus rejects 2/30/1900
Excel extends the tradition in the default date system. The 1904 date system starts the count in 1904, skipping the bad date.Excel扩展了默认日期系统的传统。1904年的日期系统从1904年开始计数，跳过坏日期。
Relative Epochs相对年代
The epoch is based on the system timezone. The epoch in New York is midnight in Eastern time, while the epoch in Seattle is midnight in Pacific time.历元基于系统时区。纽约的纪元是东部时间的午夜，而西雅图的纪元是太平洋时间的午夜。
This design has the advantage of uniform time display: "12 PM" is 12 PM irrespective of the timezone of the viewer. However, this design precludes any international coordination (there is no way to create a value that represents an absolute time) and makes JavaScript processing somewhat ambiguous (since JavaScript Date objects are timezone-aware)这种设计具有时间显示均匀的优点：“12 PM”是12 PM，与观众的时区无关。然而，这种设计排除了任何国际协调（无法创建表示绝对时间的值），并使JavaScript处理变得有些模糊（因为JavaScript Date对象具有时区意识）
This is a deficiency of the spreadsheet software. Excel has no native concept of universal time.这是电子表格软件的缺陷。Excel没有固有的世界时概念。
How Files Store Dates and Times文件如何存储日期和时间
XLS, XLSB, and most binary formats store the raw date codes. Special number formats are used to indicate that the values are intended to be dates/times.XLS、XLSB和大多数二进制格式存储原始日期代码。特殊的数字格式用于表示这些值是日期/时间。
CSV and other text formats typically store actual formatted date values. They are interpreted as dates and times in the user timezone.CSV和其他文本格式通常存储实际格式化的日期值。它们被解释为用户时区中的日期和时间。
XLSX actually supports both! Typically dates are stored as XLSX实际上支持两者！通常，日期存储为n numeric cells, but the format supports a special type d where the data is an ISO 8601 date string. n个数字单元格，但该格式支持特殊类型的d，其中数据是ISO 8601日期字符串。This is not used in the default Excel XLSX export and third-party support is poor.这在默认的Excel XLSX导出中不使用，并且第三方支持很差。
ODS does support absolute time values but drops the actual timezone indicator when parsing. In that sense, LibreOffice follows the same behavior as Excel.ODS确实支持绝对时间值，但在解析时会删除实际时区指示符。从这个意义上说，LibreOffice遵循与Excel相同的行为。
Numbers uses a calendar date system, but records pure time values as if they are absolute times in 1904 January 01. It is spiritually equivalent to the 1904 mode in Excel and other spreadsheet applications.Numbers使用日历日期系统，但记录纯时间值，就好像它们是1904年1月1日的绝对时间一样。它在精神上等同于Excel和其他电子表格应用程序中的1904模式。
How JavaScript Engines Understand TimeJavaScript引擎如何理解时间
JavaScript provides a JavaScript提供了一个Date object which represents an absolute time. Date对象，它表示一个绝对时间。Under the hood, 在引擎盖下，Date uses the "UNIX" epoch of 1970 January 01 midnight in UTC. This means the actual zero date is different in different timezones!Date使用UTC中1970年1月1日午夜的“UNIX”历元。这意味着不同时区的实际零点日期不同！
| Location | IANA Timezone | new Date(0) in local time |
|---|---|---|
| Honolulu | Pacific/Honolulu | 1969-12-31 02:00 PM |
| Los Angeles | America/Los_Angeles | 1969-12-31 04:00 PM |
| New York | America/New_York | 1969-12-31 07:00 PM |
| Sao Paulo | America/Sao_Paulo | 1969-12-31 09:00 PM |
| London | Europe/London | 1970-01-01 01:00 AM |
| Cairo | Africa/Cairo | 1970-01-01 02:00 AM |
| Djibouti | Africa/Djibouti | 1970-01-01 03:00 AM |
| Chennai | Asia/Kolkata | 1970-01-01 05:30 AM |
| Shanghai | Asia/Shanghai | 1970-01-01 08:00 AM |
| Seoul | Asia/Seoul | 1970-01-01 09:00 AM |
| Sydney | Australia/Sydney | 1970-01-01 10:00 AM |
In modern environments, the IANA Timezone and timezone offset can be discovered through the 在现代环境中，可以通过Intl and Date objects:Intl和Date对象来发现IANA时区和时区偏移：
The timezone information is provided by the JavaScript engine and local settings. There are outstanding Google Chrome and V8 bugs related to rounded offsets for timezones under a lunar calendar. 时区信息由JavaScript引擎和本地设置提供。有一些突出的谷歌Chrome和V8漏洞与农历下时区的舍入偏移有关。The last timezone to switch to the Gregorian calendar was 上一次改用公历的时区是Africa/Monrovia (in 1972).Africa/Monrovia（1972年）。
SheetJS utilities attempt to work around the browser bugs.SheetJS实用程序试图解决浏览器错误。
UTC and Local TimeUTC和当地时间
The Date object has a number of prototype methods for inspecting the object. Date对象有许多用于检查对象的原型方法。Some methods interact with the true value, while others convert to the local timezone. Some methods are listed in the table below:一些方法与真值交互，而另一些方法则转换为本地时区。下表列出了一些方法：
| Feature | ||
|---|---|---|
| Year | getFullYear | getUTCFullYear |
| Month (0-11) | getMonth | getUTCMonth |
| Day of the month | getDate | getUTCDate |
| Hours | getHours | getUTCHours |
| Minutes | getMinutes | getUTCMinutes |
| Seconds | getSeconds | getUTCSeconds |
| Entire date | toString | toUTCString |
It is typical for websites and other applications to present data in local time. To serve an international audience, backend servers typically use UTC time.网站和其他应用程序通常以本地时间显示数据。为了服务于国际受众，后端服务器通常使用UTC时间。
The following example shows the time when the page was loaded. The same absolute time will appear to be different under local and UTC interpretations:以下示例显示了加载页面的时间。在当地和UTC的解释下，相同的绝对时间似乎有所不同：
How SheetJS handles Dates and TimesSheetJS如何处理日期和时间
SheetJS attempts to reconcile the spreadsheet and JavaScript date concepts.SheetJS试图协调电子表格和JavaScript日期概念。
The default behavior for all parsers is to generate number cells. 所有解析器的默认行为都是生成数字单元格。Setting 将cellDates to true will force the parsers to store dates.cellDates设置为true将强制解析器存储日期。
When writing, date cells are automatically translated back to numeric cells with an appropriate number format.写入时，日期单元格会自动转换回具有适当数字格式的数字单元格。
The value formatting logic understands date formats and converts when relevant. It always uses the UTC interpretation of Date objects.值格式化逻辑理解日期格式，并在相关时进行转换。它总是使用日期对象的UTC解释。
Date Objects日期对象
The actual values stored in cells are intended to be correct when interpreted using UTC date methods.当使用UTC日期方法进行解释时，存储在单元格中的实际值是正确的。
For example, DateTime.xlsx is a test file with the following data:例如，DateTime.xlsx是一个包含以下数据的测试文件：
| Date | 2048-10-06 |
| Time | 15:00 |
| DateTime | 2048-10-06 15:00:00 |
The raw data values are shown in the live demo. The UTC date string will show the same value as Excel irrespective of the local timezone.原始数据值显示在实时演示中。UTC日期字符串将显示与Excel相同的值，而不考虑本地时区。
Utility Functions实用程序函数
Utility functions that deal with JS data accept a 处理JS数据的实用程序函数接受cellDates argument which dictates how dates should be handled.cellDates参数，该参数指示应如何处理日期。
Functions that create a worksheet will adjust date cells and use a number format like 创建工作表的函数将调整日期单元格，并使用数字格式（如m/d/yy to mark dates:m/d/yy）标记日期：
// Cell A1 will be a numeric cell whose value is the date code
var ws = XLSX.utils.aoa_to_sheet([[new Date()]]);
// Cell A1 will be a date cell
var ws = XLSX.utils.aoa_to_sheet([[new Date()]], { cellDates: true });
Functions that create an array of JS objects with raw values will keep the native representation:创建具有原始值的JS对象数组的函数将保留本机表示：
// Cell A1 is numeric -> output is a number
var ws = XLSX.utils.aoa_to_sheet([[new Date()]]);
var A1 = XLSX.utils.sheet_to_json(ws, { header: 1 })[0][0];
// Cell A1 is a date -> output is a date
var ws = XLSX.utils.aoa_to_sheet([[new Date()]], { cellDates: true });
var A1 = XLSX.utils.sheet_to_json(ws, { header: 1 })[0][0];
UTC OptionUTC选项
Some API functions support the 一些API函数支持UTC option to control how dates are handled.UTC选项来控制日期的处理方式。
If 如果UTC is true, the dates will be correct when interpreted in UTC. By default, the dates will be correct when interpreted in local time.UTC为true，则用UTC解释日期时，日期将是正确的。默认情况下，以当地时间解释时，日期将是正确的。
Typically 通常，UTC is used for data from an API endpoint, as servers typically emit UTC dates and expect scripts to localize. The local interpretation is sensible when users submit data, as they will be providing times in their local timezone.UTC用于API端点的数据，因为服务器通常会发出UTC日期并期望脚本本地化。当用户提交数据时，本地解释是合理的，因为他们将提供本地时区的时间。
aoa_to_sheet / sheet_add_aoa / json_to_sheet / sheet_add_json
If 如果UTC is true, the UTC interpretation of dates will be used.UTC为true，则将使用UTC对日期的解释。
Typically 通常，UTC is used for data from an API endpoint, as servers typically emit UTC dates and expect scripts to localize. The local interpretation is sensible when date objects are generated in the browser.UTC用于API端点的数据，因为服务器通常会发出UTC日期并期望脚本本地化。当在浏览器中生成日期对象时，本地解释是合理的。
table_to_book / table_to_sheet / sheet_add_dom
If 如果UTC is true, potential dates are interpreted as if they represent UTC times. By default, potential dates are interpreted in local time.UTC为true，则可能的日期被解释为表示UTC时间。默认情况下，潜在日期以当地时间解释。
Typically UTC is used for data exported from Excel or other spreadsheet apps. UTC通常用于从Excel或其他电子表格应用程序导出的数据。If the table is programmatically generated in the frontend, the dates and times will be in the local timezone and the local interpretation is preferable.如果表是在前端以编程方式生成的，则日期和时间将在本地时区中，最好使用本地解释。
Number Formats数字格式
By default, the number formats are not emitted. For Excel-based file formats, passing the option 默认情况下，不会发射数字格式。对于基于Excel的文件格式，传递选项cellNF: true adds the z field.cellNF:true会添加z字段。
The helper function 辅助函数XLSX.SSF.is_date parses formats and returns true if the format represents a date or time:XLSX.SSF.is_date解析格式，如果格式表示日期或时间，则返回true：
XLSX.SSF.is_date("yyyy-mm-dd"); // true
XLSX.SSF.is_date("0.00"); // false