# Date and time handling

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

# 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

HyperFormula offers the possibility to extend the number of supported date/time formats as well as the behavior of this functionality by exposing three 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