Lotus 1-2-3, Excel, and other spreadsheet software do not have a true conceptof 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 exposingoptions to use the traditional date codes.

The following example exports the current time to XLSX spreadsheet. The timeshown on this page will be the time displayed in Excel.

functionSheetJSNow(){const[date, setDate]=React.useState(newDate());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()}<buttononClick={()=>setDate(newDate())}>Refresh</button></p><buttononClick={xport}>Export XLSX</button></>);}

/* starting date */
const date=newDate();
/* to avoid confusion, set milliseconds to 0 */
date.setMilliseconds(0);

/* generate array of arrays */
const aoa=[[date]];

/* 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"

/* 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 shouldinclude special date and time tokens likeyyyy for long year.EDATE andother date functions operate on and return date numbers.

For date formats likeyyyy-mm-dd, the integer part represents the number ofdays from a starting epoch. For example, the date19-Feb-17 is stored as thenumber42785 with a number format ofd-mmm-yy.

The fractional part of the date code serves as the time marker. Excel assumeseach day has exactly 86400 seconds. For example, the date code0.25 has atime component corresponding to 6:00 AM.

For absolute time formats like[hh]:mm, the integer part represents a wholenumber of 24-hour (or 1440 minute) intervals. The value1.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 revealthe underlying value. Alternatively, theTEXT function can be used to returnthe date code.

The following table covers some common formats:

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

1904 and 1900 Date Systems​

The interpretation of date codes requires a shared understanding of date code0, otherwise known as the "epoch". Excel supports two epochs:

• The default epoch is "January 0 1900". The0 value is 00:00 on December 31of the year 1899, but it is formatted as January 0 1900.

• Enabling "1904 Date System" sets the default epoch to "January 1 1904". The0 value is 00:00 on January 1 of the year 1904.

The workbook's epoch can be determined by examining the workbook'swb.Workbook.WBProps.date1904 property:

if(!(wb?.Workbook?.WBProps?.date1904)){
/* uses 1904 date system */
}else{
/* uses 1900 date system */
}

@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

Relative Epochs​

The epoch is based on the system timezone. The epoch in New York is midnightin 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 PMirrespective of the timezone of the viewer. However, this design precludes anyinternational coordination (there is no way to create a value that representsan absolute time) and makes JavaScript processing somewhat ambiguous (sinceJavaScript Date objects are timezone-aware)

This is a deficiency of the spreadsheet software. Excel has no native conceptof universal time.

How Files Store Dates and Times​

How JavaScript Engines Understand Time​

JavaScript provides aDate object which represents anabsolute time. Underthe hood,Date uses the "UNIX" epoch of 1970 January 01 midnight in UTC. Thismeans the actual zero date is different in different timezones!

In modern environments, the IANA Timezone and timezone offset can be discoveredthrough theIntl andDate objects:

functionLocalInfo(){const date=newDate();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}</>)}

UTC and Local Time​

TheDate object has a number of prototype methods for inspecting the object.Some methods interact with the true value, while others convert to the localtimezone. Some methods are listed in the table below:

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 absolutetime will appear to be different under local and UTC interpretations:

functionLocalUTC(){const d=newDate();/* display number with 2 digits, prepending `0` if necessary */constf=(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. SettingcellDates to true will force the parsers to store dates.

functionSheetJSCellDates(){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 cellswith 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 interpretedusing UTC date methods.

For example,DateTime.xlsx is a test file with the following data:

The raw data values are shown in the live demo. The UTC date string will showthe same value as Excel irrespective of the local timezone.

functionSheetJSDateTimeXlsxValues(){const[data, setData]=React.useState([[]]);React.useEffect(()=>{(async()=>{const ab=await(awaitfetch("/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)=>(<trkey={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 acellDates argument whichdictates how dates should be handled.

Functions that create a worksheet will adjust date cells and use a numberformat likem/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([[newDate()]]);

// Cell A1 will be a date cell
var ws=XLSX.utils.aoa_to_sheet([[newDate()]],{cellDates:true});

Functions that create an array of JS objects with raw values will keep thenative representation:

// Cell A1 is numeric -> output is a number
var ws=XLSX.utils.aoa_to_sheet([[newDate()]]);
varA1=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([[newDate()]],{cellDates:true});
varA1=XLSX.utils.sheet_to_json(ws,{header:1})[0][0];

UTC Option​

Some API functions support theUTC option to control how dates are handled.

sheet_to_json

IfUTC is true, the dates will be correct when interpreted in UTC. By default,the dates will be correct when interpreted in local time.

TypicallyUTC is used for data from an API endpoint, as servers typically emitUTC dates and expect scripts to localize. The local interpretation is sensiblewhen 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

IfUTC is true, the UTC interpretation of dates will be used.

TypicallyUTC is used for data from an API endpoint, as servers typically emitUTC dates and expect scripts to localize. The local interpretation is sensiblewhen date objects are generated in the browser.

table_to_book /table_to_sheet /sheet_add_dom

IfUTC is true, potential dates are interpreted as if they represent UTC times.By default, potential dates are interpreted in local time.

TypicallyUTC is used for data exported from Excel or other spreadsheet apps.If the table is programmatically generated in the frontend, the dates and timeswill 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 optioncellNF: true adds thez field.

The helper functionXLSX.SSF.is_date parses formats and returnstrue if theformat represents a date or time:

XLSX.SSF.is_date("yyyy-mm-dd");// true

XLSX.SSF.is_date("0.00");// false

functionSSFIsDate(){const[format, setFormat]=React.useState("yyyy-mm-dd");const cb=React.useCallback((evt)=>{setFormat(evt.target.value);});const is_date=XLSX.SSF.is_date(format);return(<><div>Format<b>|{format}|</b> is{is_date?"":"not"} a date/time</div><inputtype="text"onChange={cb}/></>);}

How JSON and APIs Understand Time​

JSON does not have a native representation for JavaScript Date objects.

Starting from a Date object, theJSON.stringify method will encode the objectas a ISO 8601 date string. ApplyingJSON.parse to the result will return thestring rather than a proper Date object.

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 cellwhile the "Date Obj" data will be converted to a spreadsheet date.

functionSheetJSONDates(){return(<buttononClick={()=>{const aoa=[["ISO Text","2048-10-06T00:00:00.000Z"],// B1 will be text["Date Obj",newDate("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>);}

Fixing Arrays of Objects​

Many APIs will return data as JSON objects. When particular fields are known tocontain date strings, they can be manually fixed.

For example, theExport Tutorial makesa 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, thebirthday column will contain raw text values.A singleArray#map operation can create a fixed dataset:

const new_rows= rows.map(({birthday,...rest})=>({birthday:newDate(birthday),...rest}))

TheDate constructor interprets the dates in local time.