Date and time handling

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

• dateFormats,
• timeFormats,
• nullYear.

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, 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:

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.

For currency formatting in the TEXT function (locale-aware grouping, non-$ symbols, accounting patterns), see the Currency handling guide.

Demo

Run calculations Reset

Release 1.0.0	Release 4.3.1	Number of days between

Open in StackBlitz