Dates and Times
File Format Support (click to show)
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.
SheetJS attempts to create a friendly JS date experience while also exposing options to use the traditional date codes.
Date and time handling was overhauled in version 0.20.0. It is strongly
recommended to upgrade.
The following example exports the current time to XLSX spreadsheet. The time shown on this page will be the time displayed in Excel.
- React (Live Demo)
- JavaScript (Explanation)
function SheetJSNow() {
const [date, setDate] = React.useState(new Date());
const xport = React.useCallback(() => {
/* generate array of arrays */
const aoa = [[date]];
/* to avoid confusion, set milliseconds to 0 */
aoa[0][0].setMilliseconds(0);
/* generate workbook */
const ws = XLSX.utils.aoa_to_sheet(aoa, {dense: true});
/* set cell A1 number format */
ws["!data"][0][0].z = "yyyy-mm-dd hh:mm:ss"
ws["!cols"] = [{wch: 20}];
/* generate workbook and export */
const wb = XLSX.utils.book_new(ws, "Sheet1");
XLSX.writeFile(wb, "SheetJSNow.xlsx");
}, []);
return ( <>
<p>
<b>Local Time:</b>{date.toString()}
<button onClick={()=>setDate(new Date())}>Refresh</button>
</p>
<button onClick={xport}>Export XLSX</button>
</> );
}
- Create a new Date object and set milliseconds to 0 (to avoid date rounding):
/* starting date */
const date = new Date();
/* to avoid confusion, set milliseconds to 0 */
date.setMilliseconds(0);
- Construct an array of arrays to store the date. It will be placed in cell A1:
/* generate array of arrays */
const aoa = [[date]];
/* generate workbook */
const ws = XLSX.utils.aoa_to_sheet(aoa, {dense: true});
- Adjust the date format using a custom number format:
/* set cell A1 number format */
ws["!data"][0][0].z = "yyyy-mm-dd hh:mm:ss"
- Adjust the column width
/* adjust column width */
ws["!cols"] = [{wch: 20}];
/* create workbook object */
const wb = XLSX.utils.book_new(ws, "Sheet1");
/* generate XLSX workbook and attempt to download */
XLSX.writeFile(wb, "SheetJSNow.xlsx");
How Spreadsheets Understand Timeโ
Excel stores dates as numbers. When displaying dates, the format code should
include special date and time tokens like yyyy for long year. 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. For example, the date 19-Feb-17 is stored as the
number 42785 with a number format of 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 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. The value 1.5 in the format
[hh]:mm is interpreted as 36 hours 0 minutes.
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.
The following table covers some common formats:
Common Date-Time Formats (click to show)
1904 and 1900 Date Systemsโ
The interpretation of date codes requires a shared understanding of date code
0, otherwise known as the "epoch". Excel supports two epochs:
-
The default epoch is "January 0 1900". The
0value is 00:00 on December 31 of the year 1899, but it is formatted as January 0 1900. -
Enabling "1904 Date System" sets the default epoch to "January 1 1904". The
0value is 00:00 on January 1 of the year 1904.
The workbook's epoch can be determined by examining the workbook's wb.Workbook.WBProps.date1904 property:
if(!(wb?.Workbook?.WBProps?.date1904)) {
/* uses 1904 date system */
} else {
/* uses 1900 date system */
}
Why does the 1904 date system exist? (click to show)
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)
This is a deficiency of the spreadsheet software. Excel has no native concept of universal time.
How Files Store Dates and Timesโ
Technical Details (click to show)
How JavaScript Engines Understand Timeโ
JavaScript provides a Date object which represents an absolute time. 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!
| 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:
function LocalInfo() {
const date = new Date();
return ( <>
<b>Local Time</b>: {date.toString()}<br/>
<b>Time offset (relative to UTC)</b>: {-date.getTimezoneOffset()/60} hours <br/>
<b>IANA Timezone</b>: {Intl.DateTimeFormat().resolvedOptions().timeZone}
</>)}
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. The last timezone to switch to the Gregorian
calendar was Africa/Monrovia (in 1972).
SheetJS utilities attempt to work around the browser bugs.
UTC and Local Timeโ
The Date object has a number of prototype methods for inspecting the object.
Some methods interact with the true value, while others convert to the local
timezone. Some methods are listed in the table below:
| Feature | Local Time method | UTC method |
|---|---|---|
| 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.
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:
function LocalUTC() {
const d = new Date();
/* display number with 2 digits, prepending `0` if necessary */
const f = (n) => n.toString().padStart(2, "0");
/* HH:MM:SS using local interpretation */
const local = `${f(d.getHours())}:${f(d.getMinutes())}:${f(d.getSeconds())}`;
/* HH:MM:SS using UTC interpretation */
const utc = `${f(d.getUTCHours())}:${f(d.getUTCMinutes())}:${f(d.getUTCSeconds())}`;
return ( <>
<b>Local Interpretation</b><br/>
<code>toString</code>: {d.toString()}<br/>
24-hour time: {local}<br/>
<br/>
<b>UTC Interpretation</b><br/>
<code>toUTCString</code>: {d.toUTCString()}<br/>
24-hour time: {utc}<br/>
</>)}
How SheetJS handles Dates and Timesโ
SheetJS attempts to reconcile the spreadsheet and JavaScript date concepts.
The default behavior for all parsers is to generate number cells. Setting
cellDates to true will force the parsers to store dates.
function SheetJSCellDates() {
var csv = "Date,10/6/2048";
// cell B1 will be { t: 'n', v: 54337 }
var wb_sans_date = XLSX.read(csv, {type:"binary"});
var ws_sans_date = wb_sans_date.Sheets.Sheet1;
// cell B1 will be { t: 'd', v: <Date: 2048-10-06 00:00:00 UTC> }
var wb_with_date = XLSX.read(csv, {type:"binary", cellDates: true});
var ws_with_date = wb_with_date.Sheets.Sheet1;
return (<>
<b>CSV:</b><pre>{csv}</pre>
<b>Cell B1:</b><br/><br/>
<table><tr><th>cellDates</th><th>type</th><th>value</th></tr>
<tr><td>(unspecified)</td>
<td><code>{ws_sans_date["B1"].t}</code></td>
<td><code>{ws_sans_date["B1"].v}</code></td>
</tr>
<tr><td>true</td>
<td><code>{ws_with_date["B1"].t}</code></td>
<td><code>{ws_with_date["B1"].v.toISOString()}</code> (Date object)</td>
</tr>
</table>
</>);
}
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.
Date Objectsโ
The actual values stored in cells are intended to be correct when interpreted using UTC date methods.
For example, DateTime.xlsx is a test file with the following data:
| Type | Value |
|---|---|
| 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.
function SheetJSDateTimeXlsxValues() {
const [data, setData] = React.useState([[]]);
React.useEffect(() => { (async() => {
const ab = await (await fetch("/DateTime.xlsx")).arrayBuffer();
const wb = XLSX.read(ab, {cellDates: true, dense: true});
setData(wb.Sheets.Sheet1["!data"]);
})(); });
return ( <table><thead>
<th>Excel Date</th><th>UTC Date</th><th>Local Date</th>
</thead><tbody>
{data.slice(1).map((row,R) => ( <tr key={R}>
<td>{row[1].w}</td>
<td>{row[1].v.toUTCString()}</td>
<td>{row[1].v.toString()}</td>
</tr> ))}
</tbody></table> );
}
Utility Functionsโ
Utility functions that deal with JS data accept a cellDates argument which
dictates how dates should be handled.
Functions that create a worksheet will adjust date cells and use a number
format like m/d/yy to mark dates:
// 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:
// 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 Optionโ
Some API functions support the UTC option to control how dates are handled.
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.
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.
aoa_to_sheet / sheet_add_aoa / json_to_sheet / sheet_add_json
If UTC is true, the UTC interpretation of dates will be used.
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.
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.
Typically UTC is used for data exported from Excel or other spreadsheet apps.
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 cellNF: true adds the z field.
The helper function XLSX.SSF.is_date parses formats and returns true if the
format represents a date or time:
XLSX.SSF.is_date("yyyy-mm-dd"); // true
XLSX.SSF.is_date("0.00"); // false
Live Demo (click to show)
How JSON and APIs Understand Timeโ
JSON does not have a native representation for JavaScript Date objects.
Starting from a Date object, the JSON.stringify method will encode the object
as a ISO 8601 date string. Applying JSON.parse to the result will return the
string rather than a proper Date object.
flowchart LR
ws(("new Date#40;#41;"\nObject))
jstr[["'#quot;2048-10-06T00:00:00.000Z#quot;'"\nEncoded String]]
js[["#quot;2048-10-06T00:00:00.000Z#quot;"\nJavaScript String]]
ws --> |JSON\nstringify| jstr
jstr --> |JSON.parse\n\n| js
js --> |\nJSON.stringify| jstr
SheetJS utility functions will not try to interpret those strings as dates. Instead, the strings will be translated to text.
In the following example, the "ISO Text" data will be converted to a string cell while the "Date Obj" data will be converted to a spreadsheet date.
function SheetJSONDates() { return ( <button onClick={() => {
const aoa = [
["ISO Text", "2048-10-06T00:00:00.000Z"], // B1 will be text
["Date Obj", new Date("2048-10-06T00:00:00.000Z")] // B2 will be a date
];
const ws = XLSX.utils.aoa_to_sheet(aoa);
const wb = XLSX.utils.book_new(ws, "Data");
XLSX.writeFile(wb, "SheetJSONDates.xlsx");
}}>Click to Export Sample Data</button> ); }
Many API wrapper libraries return dates as strings instead of Date objects.
If the SheetJS operations generate string cells, review the documentation for the wrapper library to ensure the other library is properly handling dates.
Fixing Arrays of Objectsโ
Many APIs will return data as JSON objects. When particular fields are known to contain date strings, they can be manually fixed.
For example, the Export Tutorial makes a final array of objects with birthdays stored as strings:
const rows = [
{ name: "George Washington", birthday: "1732-02-22" },
{ name: "John Adams", birthday: "1735-10-19" },
// ... one row per President
];
If this dataset is exported, the birthday column will contain raw text values.
A single Array#map operation can create a fixed dataset:
const new_rows = rows.map(({birthday, ...rest}) => ({birthday: new Date(birthday), ...rest}))
The Date constructor interprets the dates in local time.
Excel and other spreadsheet software do not typically support dates before 1900.
If there are dates before the threshold, it is strongly recommended to pass
strings instead of Date objects.
JavaScript string to Date conversion is "implementation-dependent" and may
misinterpret some date formats. When designing APIs, it is strongly recommended
to pass ISO 8601 strings when possible.