# 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 formatstringifyDateTime
which allows for providing a function that takes the date/time and prints it as a stringstringifyDuration
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 | Bosnian | dd.MM.yyyy. |
Bosnia and Herzegovina | Serbian | dd.MM.yyyy. |
Bosnia and Herzegovina | Croatian | dd.MM.yyyy. |
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 |