Skip to main content

Dates and Times日期和时间

Lotus 1-2-3, Excel, and other spreadsheet software do not have a true concept of date or time. Lotus 1-2-3、Excel和其他电子表格软件没有真正的日期或时间概念。Instead, dates and times are stored as offsets from an epoch. 相反，日期和时间存储为与历元的偏移量。The magic behind date interpretations is hidden in functions or number formats.日期解释背后的魔力隐藏在函数或数字格式中。

SheetJS attempts to create a friendly JS date experience while also exposing options to use the traditional date codesSheetJS试图创建一个友好的JS日期体验，同时还公开了使用传统日期代码的选项

How Spreadsheets Understand Time电子表格如何理解时间​

Excel stores dates as numbers. Excel将日期存储为数字。When displaying dates, the format code should include special date and time tokens like yyyyy for long year. 显示日期时，格式代码应包括特殊的日期和时间标记，如yyyyy表示长年份。EDATE and other date functions operate on and return date numbers.和其他日期函数操作并返回日期编号。

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. Excel假设每天有86400秒。For example, the date code 0.25 has a time component corresponding to 6:00 AM.例如，日期代码0.25具有对应于上午6:00的时间分量。

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. 假设一个单元格有一个格式化的日期，将其重新格式化为“General”将显示底层值。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 hide)
Fragment碎片Interpretation理解
yyShort (2-digit) year短（2位）年份
yyyyLong (4-digit) year长（4位）年份
mShort (1-digit) month短（1位）月
mmLong (2-digit) month长（2位）月
mmmShort (3-letter) month name短（3个字母）月名
mmmmFull month name完整月份名称
mmmmmFirst letter of month name月份名称的第一个字母
dShort (1-digit) day of month短（1位）月日
ddLong (2-digit) day of month长（2位）月日
dddShort (3-letter) day of week一周中的短（3个字母）天
ddddFull day of week一周的全天
hShort (1-digit) hours短（1位）小时
hhLong (2-digit) hours长（两位数）小时
mShort (1-digit) minutes短（1位）分钟
mmLong (2-digit) minutes长（2位）分钟
sShort (1-digit) seconds短（1位）秒
ssLong (2-digit) seconds长（2位）秒
A/PMeridien ("A" or "P")Meridien（“A”或“P”）
AM/PMMeridien ("AM" or "PM")Meridien（“AM”或“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):当上一个或下一个日期标记表示时间（小时或秒）时，它被解释为“分钟”：

yyyy-mm-dd hh:mm:ss
      ^^       ^^
     month    minutes

1904 and 1900 Date Systems1904和1900日期系统​

The interpretation of date codes requires a shared understanding of date code 0, otherwise known as the "epoch". 日期代码的解释需要对日期代码0（也称为“epoch”）有共同的理解。Excel supports two epochs:Excel支持两个时代：

  • The default epoch is "January 0 1900". 默认纪元为“1900年1月0日”。The 0 value 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日”。The 0 value 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属性来确定工作簿的时代：

if ( ! ! ( ( ( wb . Workbook || { } ) . WBProps || { } ) . date1904 ) )   { 
    /* uses 1904 date system */ 
  }   else   { 
    /* uses 1900 date system */ 
  }
Why does the 1904 date system exist?为什么存在1904年的日期系统？

1900 was not a leap year. 1900年不是闰年。For the Gregorian calendar, the general rules are:对于公历，一般规则为：

  • 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每100的倍数不是400的倍数就不是闰年
  • every multiple of 4 that is not a multiple of 100 is a leap year每4的倍数不是100的倍数就是闰年
  • all other years are not leap years.其他年份都不是闰年。

Lotus 1-2-3 erroneously treated 1900 as a leap year. 莲花1-2-3错误地将1900年视为闰年。This can be verified with the @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. Excel扩展了默认日期系统中的传统。The 1904 date system starts the count in 1904, skipping the bad date.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. 这种设计具有统一时间显示的优点：“12 PM”是12 PM，与观看者的时区无关。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)然而，这种设计排除了任何国际协调（无法创建表示绝对时间的值），并使JavaScript处理有些模糊（因为JavaScript日期对象具有时区意识）

This is a deficiency of the spreadsheet software. 这是电子表格软件的一个缺陷。Excel has no native concept of universal time.Excel没有通用时间的固有概念。

The library attempts to normalize the dates. 图书馆试图使日期正常化。All times are specified in the local time zone. 所有时间均在本地时区中指定。SheetJS cannot magically fix the technical problems with Excel and other spreadsheet software, but this represents .SheetJS无法神奇地解决Excel和其他电子表格软件的技术问题，但这代表了。

How Files Store Dates and Times文件如何存储日期和时间​

XLS, XLSB, and most binary formats store the raw date codes. XLS、XLSB和大多数二进制格式存储原始日期代码。Special number formats are used to indicate that the values are intended to be dates/times.特殊数字格式用于指示值是日期/时间。

CSV and other plaintext formats typically store actual formatted date values. CSV和其他纯文本格式通常存储实际的格式化日期值。They are interpreted as dates and times in the user timezone.它们被解释为用户时区中的日期和时间。

XLSX actually supports both! XLSX实际上支持这两者！Typically dates are stored as 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. ODS确实支持绝对时间值，但在解析时会删除实际的时区指示器。In that sense, LibreOffice follows the same behavior as Excel.从这个意义上说，LibreOffice遵循与Excel相同的行为。

How SheetJS handles Dates and TimesSheetJS如何处理日期和时间​

The default behavior for all parsers is to generate number cells. 所有解析器的默认行为都是生成数字单元格。Passing the cellDates to true will force the parsers to store dates:将cellDates传递为true将强制解析器存储日期：

// cell A1 will be { t: 'n', v: 44721 } 
  var  wb_sans_date  =   XLSX . read ( "6/9/2022" ,   { type : "binary" } ) ; 
 
  // cell A1 will be { t: 'd', v: <Date object representing June 9 2022> } 
  var  wb_with_date  =   XLSX . read ( "6/9/2022" ,   { type : "binary" ,   cellDates :   true } ) ;

When writing, date cells are automatically translated back to numeric cells with an appropriate number format.写入时，日期单元格自动转换回具有适当数字格式的数字单元格。

The actual values stored in cells are intended to be correct from the perspective of an Excel user in the current timezone.从Excel用户在当前时区的角度来看，存储在单元格中的实际值是正确的。

The value formatter understands date formats and converts when relevant.值格式化程序理解日期格式，并在相关时进行转换。

Utility Functions实用程序函数​

Utility functions that deal with JS data accept a cellDates argument which dictates how dates should be handled.处理JS数据的实用程序函数接受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 ] ;

Number Formats字格式​

By default, the number formats are not emitted. 默认情况下，不会发出数字格式。For Excel-based file formats, passing the option cellNF: true adds the z field.对于基于Excel的文件格式，传递选项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
Live Demo (click to show)
Result
Loading...
Live Editor