# Formulas and functions

Starting from v4.0, the package of DHTMLX Spreadsheet includes a set of predefined formulas that can be used for different types of calculations of strings and numbers. The formulas are compatible with Excel and Google Sheets.

note

Lowercase letters in formulas are automatically converted to upper case.

## Functions​

Here's a list of all the available functions with detailed descriptions.

### Boolean operators​

You can compare two values via using logical expressions that in any given case will only return either TRUE or FALSE.

OperatorExampleDescription
==A1=B1Returns TRUE if the value in cell A1 is equal to the value in cell B1; otherwise, FALSE.
<>=A1<>B1Returns TRUE if the value in cell A1 is not equal to the value in cell B1; otherwise, FALSE.
>=A1>B1Returns TRUE if the value in cell A1 is greater than the value in cell B1; otherwise, FALSE.
<=A1<B1Returns TRUE if the value in cell A1 is less than the value in cell B1; otherwise, FALSE.
>==A1>=B1Returns TRUE if the value in cell A1 is greater than or equal to the value in cell B1; otherwise, FALSE.
<==A1<=B1Returns TRUE if the value in cell A1 is less than or equal to the value in cell B1; otherwise, FALSE.

Check the example in our snippet tool.

### Date functions​

 Function Formula Description DATE =DATE(year,month,day) Combines three separate values (year, month, and day) and returns a date. DATEDIF =DATEDIF(start_date,end_date,unit) Returns the number of days, months, or years between two dates. The unit argument is used to define which type of information you want returned. DATEVALUE =DATEVALUE(date_text) Converts a date that is stored as text to a serial number. DAY =DAY(date) Returns the day of the month as a number between 1 to 31 from a specified date. DAYS =DAYS(end_date, start_date) Returns the number of days between two dates. DAYS360 =DAYS360(start_date,end_date,[method]]) Returns the number of days between 2 dates, based on a 360-day year (twelve 30-days months). EDATE =EDATE(start_date, months) Returns the date on the same date of the month, n months in the past or future. EOMONTH =EOMONTH(start_date, months) Returns the date for the last day of the month, n months before or after the specified start date. ISOWEEKNUM =ISOWEEKNUM(date) Returns the number of the ISO week number of the year for the specified date. MONTH =MONTH(date) Returns the month of the year of the specified date. NETWORKDAYS =NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole working days between two dates. Working days exclude weekends and any dates specified in holidays. NETWORKDAYSINTL =NETWORKDAYSINTL(start_date, end_date, [weekend], [holidays]) Returns the number of whole working days between two dates. The optional weekend parameter is used to specify which days of the week are considered weekends. Weekend days and holidays are not considered as workdays. NOW =NOW() Returns the current date. TIMEVALUEadded in v4.3 =TIMEVALUE(time_text) Returns the decimal number of the time represented by a text string WEEKDAY =WEEKDAY(date,[return_type]) Returns the day of the week for the specified date. The return_type argument is used to define which day of the week is considered the first day. WEEKNUM =WEEKNUM(date,[return_type]) Returns the week number for the specified date. The return_type argument is used to define which day of the week is considered the first day. WORKDAY =WORKDAY(start_date, days, [holidays]) Returns the date of the nearest working day n days in the future or past.Working days exclude weekends and any dates specified in holidays. WORKDAYINTL =WORKDAYINTL(start_date, days, [weekend], [holidays]) Returns the date of the nearest working day n days in the future or past. The optional weekend parameter is used to specify which days of the week are considered weekends. Weekend days and holidays are not considered as workdays. YEAR =YEAR(date) Returns the year of the specified date. YEARFRAC =YEARFRAC(start_date, end_date, [basis]) Returns the year of the specified date. The optional basis argument is used to define the type of day count basis.

Check the example in our snippet tool.

### Financial functions​

Check the example in our snippet tool.

### Information functions​

 Function Formula Description ISBINARY =ISBINARY(value) Returns TRUE if the value is binary; otherwise, returns FALSE. ISBLANK =ISBLANK(A1) Returns TRUE if a cell is empty; otherwise, returns FALSE. ISEVEN =ISEVEN(number) Returns TRUE if a number is even, or FALSE if number is odd. Works with integer numbers. ISNONTEXT =ISNONTEXT(value) Returns TRUE if a cell contains any value except text; otherwise, returns FALSE. ISNUMBER =ISNUMBER(value) Returns TRUE if a cell contains a number; otherwise, returns FALSE. ISODD =ISODD(number) Returns TRUE if a number is odd, or FALSE if number is even. Works with integer numbers. ISTEXT =ISTEXT(value) Returns TRUE if a value is text; otherwise, returns FALSE. N =N(value) Returns a value converted to a number.

Check the example in our snippet tool.

### Math functions​

Check the example in our snippet tool.

### Regex functions​

 Function Formula Description REGEXREPLACE =REGEXREPLACE(text, regular_expression, replacement) Replaces a part of a text string with a different text string using regular expressions. REGEXMATCH =REGEXMATCH(text, regular_expression) Returns TRUE if a text string matches the pattern in the regular expression and FALSE if it doesn't. REGEXEXTRACT =REGEXEXTRACT(text, regular_expression) Returns the part of the string that matches the pattern in the regular expression.

Check the example in our snippet tool.

### String functions​

 Function Formula Description ARRAYTOTEXTadded in v4.3 =ARRAYTOTEXT(array, [format]) Returns an array of text values from any specified range, based on the format you specify (0 - concise (default) or 1 - strict format) CHAR =CHAR(number) Returns the character (from the character set used by your computer) specified by a number. Number must be between 1 and 255. CLEAN =CLEAN(text) Removes characters, which are not printed when you use the print option, from the text. CODE =CODE(text) Returns a numeric code for the first character in a text string. CONCATENATE =CONCATENATE(A1,"",B2:C3) Joins two or more text strings into one string. DOLLAR =DOLLAR(number, decimals) Converts a number to text using the currency format, based on the number of digits to the right/left of the decimal point you specify (by default, 2). EXACT =EXACT(text1, text2) Compares two strings and returns TRUE if they are exactly the same; otherwise, returns FALSE. FIND =FIND(find_text, within_text, [start_num]) Returns the position (as a number) of one text string inside another, starting at the position you specify (by default, 1). FIXED =FIXED(number, [decimals], [no_commas]) Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and converts the result to text. If no_commas is set to 1, the returned text won't include commas. JOIN =JOIN(separator, value1, value2,...) Concatenates values using a specified separator. LEFT =LEFT(text, count) Returns the first character or characters in a text string, based on the number of characters you specify. LEN =LEN(text) Returns the length of the specified string. LOWER =LOWER(text) Converts all letters in the specified string to lowercase. MID =MID(text, start, count) Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. NUMBERVALUE =NUMBERVALUE (text, [decimal_separator], [group_separator]) Converts a number in text format to numeric value, using specified decimal and group separators. PROPER =PROPER(text) Sets the first character in each word to uppercase and converts all other characters to lowercase. REPLACEadded in v4.3 =REPLACE(old_text, start_num, num_chars, new_text), where:old_text - the text in which you want to replace some characters;start_num - the position of the character in old_text that you want to replace with new_text;num_chars - the number of characters to be replaced in old_text;new_text - the text that will replace characters in old_text. Replaces part of a text string, based on the number of characters you specify, with a different text string. REPT =REPT(text, number_times) Repeats text a specified number of times. RIGHT =RIGHT(text, count) Returns the last character or characters (rightmost) in a text string, based on the number of characters you specify. SEARCH =SEARCH(find_text, within_text, [start_num]) Returns the position (as a number) of the first character of find_text inside within_text, starting at the position you specify (by default, 1). SUBSTITUTE =SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces old text with new text in a text string. If you specify instance_num, only that instance of old_text is replaced; otherwise, all instances are replaced. T =T(value) returns text when given a text value and an empty string ("") for numbers, dates, and the logical TRUE/FALSE values. TRIM =TRIM(text) Removes all spaces from text except for single spaces between words. UPPER =UPPER(text) Converts text to uppercase.

Check the example in our snippet tool.

### Other functions​

 Function Example Description AND =AND(logical1, [logical2], ...) Returns either TRUE or FALSE depending on whether multiple conditions are met or not. CHOOSE =CHOOSE(index_num, value1, [value2], ...) Returns a value from the list of value arguments based on a position or index you specify. FALSE =FALSE() Returns the logical value FALSE. IF =IF(condition, [value_if_true], [value_if_false]) Returns one value if a condition is TRUE and another value if it's FALSE. NOT =NOT(logical) Returns the opposite of a given logical or Boolean value. OR =OR(logical1, [logical2], ...) Returns TRUE if at least one of the logical expressions is TRUE; otherwise, FALSE. TRUE =TRUE() Returns the logical value TRUE.

Check the example in our snippet tool.

## Getting cell formula​

Starting with v4.1, you can get the formula applied to a cell via the getFormula() method. The method takes the id of the cell as a parameter:

``var formula = spreadsheet.getFormula("B2");// -> "ABS(C2)"``