HyperFormula (v3.1.0)

# Date and time handling

The formats for the default date and time parsing functions can be set using configuration options:

The API reference of dateFormats and timeFormats describes the supported date and time formats in detail.

# Example

By default, HyperFormula uses the European date and time formats.

dateFormats: ['DD/MM/YYYY', 'DD/MM/YY'], // set by default timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default

To use the US date and time formats, set:

dateFormats: ['MM/DD/YYYY', 'MM/DD/YY', 'YYYY/MM/DD'], // US date formats timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default

# Custom date and time handling

If date and time formats supported by the dateFormats and timeFormats parameters are not enough, you can extend them by providing the following options:

  • parseDateTime, which allows to provide a function that accepts a string representing date/time and parses it into an actual date/time format
  • stringifyDateTime, which allows to provide a function that takes the date/time and prints it as a string
  • stringifyDuration, which allows to provide a function that takes time duration and prints it as a string

To extend the number of possible date formats, you will need to configure parseDateTime . This functionality is based on callbacks, and you can customize the formats by integrating a third-party library like Moment.js (opens new window), or by writing your own custom function that returns a DateTime object.

The configuration of date formats and stringify options may impact some built-in functions. For instance, the VALUE function transforms strings into numbers, which means it uses parseDateTime. The TEXT function works the other way round - it accepts a number and returns a string, so it uses stringifyDateTime. Any change here might give you different results. Criteria-based functions (SUMIF, AVERAGEIF, etc.) perform comparisons, so they also need to work on strings, dates, etc.

# Moment.js integration

In this example, you will add the possibility to parse dates in the "Do MMM YY" custom format.

To do so, you first need to write a function using Moment.js API (opens new window):

import moment from "moment"; // write a custom function for parsing dates export const customParseDate = (dateString, dateFormat) => { const momentDate = moment(dateString, dateFormat, true); // check validity of a date with moment.js method if (momentDate.isValid()) { return { year: momentDate.year(), month: momentDate.month() + 1, day: momentDate.date() }; } // if the string was not recognized as // a valid date return nothing return undefined; };

Then, use it inside the configuration options like so:

const options = { parseDateTime: customParseDate, // you can add more formats dateFormats: ["Do MMM YY"] };

After that, you should be able to add a dataset with dates in your custom format:

const data = [["31st Jan 00", "2nd Jun 01", "=B1-A1"]];

And now, HyperFormula recognizes these values as valid dates and can operate on them.

# Demo

Release 1.0.0 Release 4.3.1 Number of days between

← Compatibility with Microsoft Excel →