# Built-in functions
# Overview
HyperFormula comes with an extensive library of pre-built functions. You can use them to create complex formulas for any business application. Formula syntax and logic of function are similar to what is considered the standard in modern spreadsheet software. That is because a spreadsheet is probably the most universal software ever created. We wanted the same flexibility for HyperFormula but without the constraints of the spreadsheet UI.
Each of HyperFormula's built-in function names is available in 17 languages and custom language packs can be added.
The latest version of HyperFormula has an extensive collection of 423 functions grouped into categories:
- Array manipulation
- Database
- Date and time
- Engineering
- Financial
- Information
- Logical
- Lookup and reference
- Math and trigonometry
- Matrix functions
- Operator
- Statistical
- Text
Some categories such as compatibility and cube are yet to be supported.
TIP
You can modify the built-in functions or create your own, by adding a custom function.
# List of available functions
Total number of functions: 423
# Array manipulation
# Database
# Date and time
| Function ID | Description | Syntax |
|---|---|---|
| DATE | Returns the specified date as the number of full days since nullDate (opens new window). | DATE(year, month, day) |
| DATEDIF | Calculates distance between two dates. Supported units: "D" (days), "M" (months), "Y" (years), "MD" (days ignoring months and years), "YM" (months ignoring years), or "YD" (days ignoring years). | DATEDIF(start_date, end_date, unit) |
| DATEVALUE | Parses date_string and returns it as the number of full days since nullDate (opens new window).Accepts formats set by the dateFormats (opens new window) option. | DATEVALUE(date_string) |
| DAY | Returns the day of the given date value. | DAY(number) |
| DAYS | Calculates the difference between two date values. | DAYS(end_date, start_date) |
| DAYS360 | Calculates the difference between two date values in days, in 360-day basis. | DAYS360(start_date, end_date, [format]) |
| EDATE | Shifts start_date by the given number of months and returns it as the number of full days since nullDate (opens new window).The return value complies with the OpenDocument standard, but the return type does not; see the compatibility notes (opens new window). | EDATE(start_date, months) |
| EOMONTH | Returns the date of the last day of the month that falls the given number of months away from start_date. Returns the value in the form of number of full days since nullDate (opens new window).The return value complies with the OpenDocument standard, but the return type does not; see the compatibility notes (opens new window). | EOMONTH(start_date, months) |
| HOUR | Returns hour component of given time. | HOUR(time) |
| INTERVAL | Returns interval string from given number of seconds. | INTERVAL(seconds) |
| ISOWEEKNUM | Returns an ISO week number that corresponds to the week of year. | ISOWEEKNUM(date) |
| MINUTE | Returns minute component of given time. | MINUTE(time) |
| MONTH | Returns the month for the given date value. | MONTH(number) |
| NETWORKDAYS | Returns the number of working days between two given dates. | NETWORKDAYS(date1, date2, [holidays]) |
| NETWORKDAYS.INTL | Returns the number of working days between two given dates, with a configurable set of weekend days. | NETWORKDAYS.INTL(date1, date2, [mode], [holidays]) |
| NOW | Returns current date + time as a number of days since nullDate (opens new window). | NOW() |
| SECOND | Returns second component of given time. | SECOND(time) |
| TIME | Returns the number that represents a given time as a fraction of full day. | TIME(hour, minute, second) |
| TIMEVALUE | Parses time_string and returns a number that represents it as a fraction of a full day. Accepts formats set by the timeFormats (opens new window) option. | TIMEVALUE(time_string) |
| TODAY | Returns an integer representing the current date as the number of full days since nullDate (opens new window). | TODAY() |
| WEEKDAY | Computes a number between 1-7 representing the day of week. | WEEKDAY(date, [type]) |
| WEEKNUM | Returns a week number that corresponds to the week of year. | WEEKNUM(date, [type]) |
| WORKDAY | Returns the date a given number of working days after the start date, skipping Saturdays and Sundays. | WORKDAY(date, shift, [holidays]) |
| WORKDAY.INTL | Returns the date a given number of working days after the start date, with a configurable set of weekend days. | WORKDAY.INTL(date, shift, [mode], [holidays]) |
| YEAR | Returns the year as a number according to the internal calculation rules. | YEAR(number) |
| YEARFRAC | Computes the difference between two date values, in fraction of years. | YEARFRAC(start_date, end_date, [format]) |
# Engineering
# Financial
# Information
# Logical
# Lookup and reference
| Function ID | Description | Syntax |
|---|---|---|
| ADDRESS | Returns a cell reference as a string. | ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) |
| CHOOSE | Uses an index to return a value from a list of values. | CHOOSE(index, value1, ...) |
| COLUMN | Returns column number of a given reference or formula reference if argument not provided. | COLUMN([reference]) |
| COLUMNS | Returns the number of columns in the given reference. | COLUMNS(array) |
| FORMULATEXT | Returns a formula in a given cell as a string. | FORMULATEXT(reference) |
| HLOOKUP | Searches horizontally with reference to adjacent cells to the bottom. | HLOOKUP(search_criterion, array, index, [sort_order]) |
| HYPERLINK | Stores the url in the cell's metadata. It can be read using method getCellHyperlink (opens new window) | HYPERLINK(url, [link_label]) |
| INDEX | Returns the contents of a cell specified by row and column number. The column number is optional and defaults to 1. | INDEX(range, row, [column]) |
| MATCH | Returns the relative position of an item in an array that matches a specified value. | MATCH(search_criterion, lookup_array, [match_type]) |
| OFFSET | Returns the value of a cell offset by a certain number of rows and columns from a given reference point. | OFFSET(reference, rows, columns, [height], [width]) |
| ROW | Returns row number of a given reference or formula reference if argument not provided. | ROW([reference]) |
| ROWS | Returns the number of rows in the given reference. | ROWS(array) |
| VLOOKUP | Searches vertically with reference to adjacent cells to the right. | VLOOKUP(search_criterion, array, index, [sort_order]) |
| XLOOKUP | Searches for a key in a range and returns the item corresponding to the match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
# Math and trigonometry
# Matrix functions
# Operator
# Statistical
# Text
| Function ID | Description | Syntax |
|---|---|---|
| CHAR | Converts a number into a character according to the current code table. | CHAR(number) |
| CLEAN | Returns text that has been "cleaned" of line breaks and other non-printable characters. | CLEAN(text) |
| CODE | Returns a numeric code for the first character in a text string. | CODE(text) |
| CONCATENATE | Combines several text strings into one string. | CONCATENATE(text1, ...) |
| EXACT | Returns TRUE if both text strings are exactly the same. | EXACT(text1, text2) |
| FIND | Returns the location of one text string inside another. | FIND(search_string, text, [start_position]) |
| LEFT | Extracts a given number of characters from the left side of a text string. | LEFT(text, [number]) |
| LEN | Returns length of a given text. | LEN(text) |
| LOWER | Returns text converted to lowercase. | LOWER(text) |
| MID | Returns a substring of a given length starting from start_position. | MID(text, start_position, length) |
| PROPER | Capitalizes words given text string. | PROPER(text) |
| REPLACE | Replaces substring of a text of a given length that starts at given position. | REPLACE(text, start_position, length, new_text) |
| REPT | Repeats text a given number of times. | REPT(text, number) |
| RIGHT | Extracts a given number of characters from the right side of a text string. | RIGHT(text, [number]) |
| SEARCH | Returns the location of search_string inside text. Case-insensitive. Allows the use of wildcards. | SEARCH(search_string, text, [start_position]) |
| SPLIT | Divides the provided text using the space character as a separator and returns the substring at the zero-based position specified by the second argument. For example, SPLIT("Lorem ipsum", 0) returns "Lorem" and SPLIT("Lorem ipsum", 1) returns "ipsum". | SPLIT(text, index) |
| SUBSTITUTE | Returns a string where occurrences of old_text are replaced by new_text. Replaces only specific occurrence if last parameter is provided. | SUBSTITUTE(text, old_text, new_text, [occurrence]) |
| T | Returns text if given value is text, empty string otherwise. | T(value) |
| TEXT | Converts a number into text according to a given format. By default it accepts the same formats as the dateFormats (opens new window) option, and can be further customized with the stringifyDateTime (opens new window) and stringifyCurrency (opens new window) options. | TEXT(number, format) |
| TEXTJOIN | Joins text from multiple strings and/or ranges with a delimiter. Supports array/range delimiters that cycle through gaps. When ignore_empty is TRUE, empty strings are skipped. Returns #VALUE! if result exceeds 32,767 characters. | TEXTJOIN(delimiter, ignore_empty, text1, ...) |
| TRIM | Strips extra spaces from text. | TRIM(text) |
| UNICHAR | Returns the character created by using provided code point. | UNICHAR(number) |
| UNICODE | Returns the Unicode code point of a first character of a text. | UNICODE(text) |
| UPPER | Returns text converted to uppercase. | UPPER(text) |
| VALUE | Parses a number, date, time, datetime, currency, or percentage from a text string. | VALUE(text) |