HyperFormula (v0.6.2)

# Date and time handling

Date and time formats can be set in the configuration options.

dateFormats is a list of formats supported by the parser inside HyperFormula. The default format is ['MM/DD/YYYY', 'MM/DD/YY']. The separator is ignored and it can be any of the following: '-' (dash), ' ' (empty space), '/' (slash).

Similar to dateFormats, timeFormats is a list of time formats supported by the parser. The default format is ['hh:mm', 'hh:mm:ss.sss']. The only accepted separator is ':' (colon).

# Example with Chinese

const options = {
    // add popular date format used in China
    dateFormats: ['yyyy-M-d', 'MM/DD/YYYY', 'MM/DD/YY'],
    // add a custom time format
    timeFormats: ['hh:mm:ss', 'hh:mm', 'hh:mm:ss.sss'],
};

# Integration and customization

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 for providing a function that accepts a string representing date/time and parses it into an actual date/time format
  • stringifyDateTimewhich allows for providing a function that takes the date/time and prints it as a string
  • stringifyDuration which allows for providing 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 like this:

 {
  year: number,
  month: number,
  day: number,
 }

The configuration of date formats and stringify options may have an impact on how the functions TEXT and VALUE, as well as criterions (e.g. SUMIF, AVERAGEIF), will behave. For instance, VALUE accepts a string and returns a number, which means it uses parseDatetime. TEXT 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. Criterions do 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 the operations on dates are possible since HyperFormula recognizes them as proper dates:

# Demo



# Cheat sheet

Below is a cheat sheet with the most popular date formats in different countries.

Country Language Format
Albania Albanian yyyy-MM-dd
United Arab Emirates Arabic dd/MM/yyyy
Argentina Spanish dd/MM/yyyy
Australia English d/MM/yyyy
Austria German dd.MM.yyyy
Belgium French d/MM/yyyy
Belgium Dutch d/MM/yyyy
Bulgaria Bulgarian yyyy-M-d
Bahrain Arabic dd/MM/yyyy
Bosnia and Herzegovina Serbian yyyy-MM-dd
Belarus Belarusian d.M.yyyy
Bolivia Spanish dd-MM-yyyy
Brazil Portuguese dd/MM/yyyy
Canada French yyyy-MM-dd
Canada English dd/MM/yyyy
Switzerland German dd.MM.yyyy
Switzerland French dd.MM.yyyy
Switzerland Italian dd.MM.yyyy
Chile Spanish dd-MM-yyyy
China Chinese yyyy-M-d
Colombia Spanish d/MM/yyyy
Costa Rica Spanish dd/MM/yyyy
Cyprus Greek dd/MM/yyyy
Czech Republic Czech d.M.yyyy
Germany German dd.MM.yyyy
Denmark Danish dd-MM-yyyy
Dominican Republic Spanish MM/dd/yyyy
Algeria Arabic dd/MM/yyyy
Ecuador Spanish dd/MM/yyyy
Egypt Arabic dd/MM/yyyy
Spain Spanish d/MM/yyyy
Spain Catalan dd/MM/yyyy
Estonia Estonian d.MM.yyyy
Finland Finnish d.M.yyyy
France French dd/MM/yyyy
United Kingdom English dd/MM/yyyy
Greece Greek d/M/yyyy
Guatemala Spanish d/MM/yyyy
Hong Kong Chinese yyyy年M月d日
Honduras Spanish MM-dd-yyyy
Croatia Croatian dd.MM.yyyy.
Hungary Hungarian yyyy.MM.dd.
Indonesia Indonesian dd/MM/yyyy
India Hindi ३/६/१२
India English d/M/yyyy
Ireland Irish dd/MM/yyyy
Ireland English dd/MM/yyyy
Iraq Arabic dd/MM/yyyy
Iceland Icelandic d.M.yyyy
Israel Hebrew dd/MM/yyyy
Italy Italian dd/MM/yyyy
Jordan Arabic dd/MM/yyyy
Japan Japanese yyyy/MM/dd
Japan Japanese H24.MM.dd
South Korea Korean yyyy. M. d
Kuwait Arabic dd/MM/yyyy
Lebanon Arabic dd/MM/yyyy
Libya Arabic dd/MM/yyyy
Lithuania Lithuanian yyyy.M.d
Luxembourg French dd/MM/yyyy
Luxembourg German dd.MM.yyyy
Latvia Latvian yyyy.d.M
Morocco Arabic dd/MM/yyyy
Mexico Spanish d/MM/yyyy
Macedonia Macedonian d.M.yyyy
Malta English dd/MM/yyyy
Malta Maltese dd/MM/yyyy
Montenegro Serbian d.M.yyyy.
Malaysia Malay dd/MM/yyyy
Nicaragua Spanish MM-dd-yyyy
Netherlands Dutch d-M-yyyy
Norway Norwegian dd.MM.yyyy
Norway Norwegian dd.MM.yyyy
New Zealand English d/MM/yyyy
Oman Arabic dd/MM/yyyy
Panama Spanish MM/dd/yyyy
Peru Spanish dd/MM/yyyy
Philippines English M/d/yyyy
Poland Polish dd.MM.yyyy
Puerto Rico Spanish MM-dd-yyyy
Portugal Portuguese dd-MM-yyyy
Paraguay Spanish dd/MM/yyyy
Qatar Arabic dd/MM/yyyy
Romania Romanian dd.MM.yyyy
Russia Russian dd.MM.yyyy
Saudi Arabia Arabic dd/MM/yyyy
Serbia and Montenegro Serbian d.M.yyyy.
Sudan Arabic dd/MM/yyyy
Singapore Chinese dd/MM/yyyy
Singapore English M/d/yyyy
El Salvador Spanish MM-dd-yyyy
Serbia Serbian d.M.yyyy.
Slovakia Slovak d.M.yyyy
Slovenia Slovenian d.M.yyyy
Sweden Swedish yyyy-MM-dd
Syria Arabic dd/MM/yyyy
Thailand Thai d/M/2555
Thailand Thai ๓/๖/๒๕๕๕
Tunisia Arabic dd/MM/yyyy
Turkey Turkish dd.MM.yyyy
Taiwan Chinese yyyy/M/d
Ukraine Ukrainian dd.MM.yyyy
Uruguay Spanish dd/MM/yyyy
United States English M/d/yyyy
United States Spanish M/d/yyyy
Venezuela Spanish dd/MM/yyyy
Vietnam Vietnamese dd/MM/yyyy
Yemen Arabic dd/MM/yyyy
South Africa English yyyy/MM/dd

← Key concepts →