--- title: "Overview of Stackby Formulas" description: "Let's learn about one of the most powerful column types in Stackby: Formulas and all the formulas there are" url: https://help.stackby.com/en/articles/12-overview-of-stackby-formulas collection: "Using Formulas" updated: 2025-05-31 language: en --- # Overview of Stackby Formulas Let's learn about one of the most powerful column types in Stackby: Formulas and all the formulas there are ## Stackby Formulas Tutorial **Take this 30-minutes live tutorial to learn everything about Stackby Formulas:**  ## **Formulas**  You can represent a single column type in a different way or compute between two or more different column types (Text or Number) using Formulas.  It may involve numerical operations, logical operations, functions and text operations that operate on columns.  ## **How are they different from spreadsheet formulas?**  Unlike spreadsheet formulas, that operate on individual cell (A2, B3), **formulas in Stackby operate on each column (defined by column name).** It functions like a database, and columns are first-class citizen instead of cells.  **Watch the video on Formulas and Calculations in Stackby:**  ## **Types of Formulas**  - Numerical operators & Functions - Logical operators & Functions - Date and Time Functions - Text Functions  - Array Functions (Only used in Aggregation column type)  - REGEX Formulas - Other Formulas  A note about formula syntax: - All functions are denoted in upper case and the parameters required for them are surrounded by round brackets ( ) - All Optional parameters are surrounded by square brackets \[ \] - All column type parameters are surrounded by curved braces { } **Below is the table for all the formulas in Stackby for your reference. Copy this stack and add it your workspace for reference.**  ### **Numerical operators & Functions** **_Numerical operators:_** - Numeric operators are used with logical functions - Numerical operators return a numeric value  - We support 4 numerical operators ```javascript '+' : Adds two numerical values Example: {Column Name} + 3  ``` ```javascript '-' : Subtracts two numerical values Example: {Column Name} - 3.00  ``` ```javascript '*' : Multiply two numerical values Example: {Size}*{Price} ``` ```javascript '/' - Divide two numerical values Example: {Weight}/{Height} ``` **_Numerical functions:_**  ```javascript ABS(value) : Returns the absolute value. ``` ```javascript AVERAGE(number1, [number2, ...]) : Returns the average of the numbers. ``` ```javascript CEILING(value, [significance]) : Returns the nearest integer multiple of significance that is greater than or equal to the value. If no significance is provided, a significance of 1 is assumed. ``` ```javascript COUNT(number1, [number2, ....]) : Count the number of numeric items. ``` ```javascript COUNTA(textOrNumber1, [number2, ....]) : Count the number of non-empty values. This function counts both numeric and text values. ``` ```javascript COUNTALL(textOrNumber1, [number2, ....]) : Count the number of all elements including text and blanks. ``` ```javascript EVEN(value) : Returns the smallest even integer that is greater than or equal to the specified value. ``` ```javascript EXP(power) : Computes Euler's number (e) to the specified power. ``` ```javascript FLOOR(value, [significance]) : Returns the nearest integer multiple of significance that is less than or equal to the value. If no significance is provided, a significance of 1 is assumed. ``` ```javascript INT(value) : Returns the greatest integer that is less than or equal to the specified value. ``` ```javascript LOG(number, base=10) : Computes the logarithm of the value in provided base. The base defaults to 10 if not specified. ``` ```javascript MAX(number1, [number2, ...]) : Returns the largest of the given numbers. ``` ```javascript MIN(number1, [number2, ...]) : Returns the smallest of the given numbers. ``` ```javascript MOD(value1, divisor) : Returns the remainder after dividing the first argument by the second. ``` ```javascript ODD(value) : Rounds positive value up the the nearest odd number and negative value down to the nearest odd number. ``` ```javascript POWER(base, power) : Computes the specified base to the specified power. ``` ```javascript ROUND(value, precision) : Rounds the value to the number of decimal places given by "precision." (Specifically, ROUND will round to the nearest integer at the specified precision, with ties broken by rounding half up toward positive infinity.) ``` ```javascript ROUNDDOWN(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding down, i.e., toward zero. (You must give a value for the precision or the function will not work.) ``` ```javascript ROUNDUP(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding up, i.e., away from zero. (You must give a value for the precision or the function will not work.) ``` ```javascript SQRT(value) : Returns the square root of a nonnegative number. ``` ```javascript SUM(number1, [number2, ...]) : Sum together the numbers. Equivalent to number1 + number2 + ... ``` ```javascript VALUE(text) : Converts the text string to a number. ``` ```javascript SUB({column1}, {number2}): Subtract the number. Equivalent to (value of column1) - (value of column2) ``` ### **Logical Operators & Functions** **_Logical operators:_** - Logical operators are used with logical functions - Logical operators return a TRUE or FALSE value when compared with the data in cells. This value is then used by logical functions for further processing. - We support 6 logical operators ```javascript '>' : Greater than Example: {ColumnName} > 15 '<' : Less than Example: {ColumnName} < 15 '>=' : Greater than or Equal to Example: {ColumnName} >= 15 '<=' : Less than of Equal to Example: {ColumnName} <= 15 '==' : Equal to Example: {ColumnName} == 15 '!=' : Not Equal to Example: {ColumnName} != 15 ``` **_Logical functions:_** ```javascript AND(logical1, [logical2, ...]) : Returns true if all the arguments are true, returns false otherwise. Example: AND({Employee}, {Employer}) ``` ```javascript BLANK() : Returns a blank value. Example: IF({Value} > 1000000, "Value less than 1000000", "Value more than 1000000") ``` ```javascript ERROR() : Returns the error value. Example: IF({Grade} == "F", ERROR(), "OK") ``` ```javascript FALSE() : Logical value false. ``` ```javascript IF(, , ) : Returns 'exp1' if the logical argument is true, otherwise it returns 'exp2'. - Exp1/Exp2 can either be values, text, or other conditions - You can also add more IF statements in exp1/exp2 - Can also be used to check if a cell is blank/is empty. Example: - IF({GPA} > 2.5, "Good", "Bad") - IF({Date} = BLANK(), "Please enter date", "Date entered") - IF({Sky} == "Clear", IF({Temp} < 30, "Great weather!", "I'm burning!"), "Disappointing Weather.") ``` ```javascript ISERROR(expr) : Returns true if the expression causes an error. Example: ISERROR(SQRT(-99)) ``` ```javascript NOT(boolean) : Reverses the logical value of its argument. Example: NOT(Total > 50) ``` ```javascript OR(logical1, [logical2, ...]) : Returns true if any one of the arguments is true. Example: OR({Fire}, {Smoke}) ``` ```javascript TRUE() : Logical value true. Example: IF({Value} > 50, TRUE(), FALSE()) ``` ```javascript XOR(logical1, [logical2, ...]) : Returns true if an odd number of arguments are true. Example: XOR(TRUE(), {Complete}, {Incomplete}) ``` ```javascript SWITCH(): It is an alternative to IF statement. Example: SWITCH({Name},4, ‘Planning’, 3, ‘Execution’, 2, ‘Review’, 1, ‘Launch’, ‘Out of range’) ``` ### **Text Functions** ```javascript CONCAT() : Concatenates all the column values together Example: CONCAT({Column1},{Column 2},{Column 3}) Note: If you need space between columns, you need add it like below CONCAT({Column1}," ",{Column 2}," ",{Column 3}) ``` ```javascript SUBSTITUTE() : Replaces occurrences of old_text with new_text. Example: SUBSTITUTE("gold mold", "old", "et")  -> get met Note: You can optionally specify an index number (starting from 1) to replace just a specific occurrence of old_text. If no index number is specified, then all occurrences of old_text will be replaced. SUBSTITUTE("gold mold", "old", "et", 1) -> get mold ``` ```javascript UPPER() : Makes string uppercase. Example: UPPER("Hello!") => HELLO! ``` ```javascript LOWER() : Makes string lowercase. Example: LOWER("Hello!") => hello! ``` ```javascript T() : Returns the argument if it is text and blank otherwise. Example: T("text only")  => text only  T(42) => blank ``` ```javascript REPT(): This function repeats any references string by a specified number of times. Example: REPT("Hi!",2) => Hi!Hi! ``` ```javascript LEN() : Returns the length of a string. Example: LEN("quick brown fox")  => 15 ``` ```javascript REPLACE() : Replaces the number of characters beginning with the start character with the replacement text. Example: REPLACE("database", 2, 5, "o")  => dose Note: If you're looking for a way to find and replace all occurrences of old_text with new_text, see SUBSTITUTE(). ``` ```javascript SEARCH() : Searches for an occurrence of stringToFind in whereToSearch string starting from an optional startFromPosition. For no occurence, Result is Empty Example: SEARCH("Hello World","World")  => 7 Note: Similar to FIND(), though FIND() returns 0 rather than empty if no occurrence of stringToFind is found. ``` ```javascript RIGHT() : Extract howMany characters from the end of the string. Example: RIGHT("quick brown fox", 5)  => n fox ``` ```javascript LEFT() : Extract how Many characters from the beginning of the string. Example: LEFT("quick brown fox", 5)  => quick ``` ```javascript MID() : Extract a substring of count characters starting at whereToStart. Example: MID("quick brown fox", 6, 5)  => brown ``` ```javascript TRIM() : Removes whitespace at the beginning and end of string. Example: TRIM(" Hello! ")  => Hello! ``` ### Date and Time Functions ```javascript NOW () : Returns the current time and day. Example: DATE_ADD(NOW(), 5, "days") ``` ```javascript TONOW() : Displays relative time to now. Example : TONOW({Deadline}) ``` ```javascript FROMNOW() : Displays relative time from now. Example: FROMNOW({Deadline}) ``` ```javascript CREATED_TIME() : Returns the creation time of the current record. Example: CREATED_TIME() ``` ```javascript DATE_ADD() : Adds a "count" units to Datetime, such as DATE_ADD(Datetime, count, unit) Example: DATE_ADD(Datetime, 5, "days") ``` ```javascript DATE_SUB() : Substracts a "count" units to Datetime such as DATE_SUB(Datetime, count, unit) Example: DATE_SUB(Datetime, 5, "days") ``` ```javascript DATE_DIFF() : Returns the difference between datetimes in specified units such as DATE_DIFF(From, To, units) Example: DATE_DIFF({Deadline}, NOW(), "days") ``` ```javascript DATETIME_FORMAT(): Formats a datetime into a specified string such as DATETIME_FORMAT(Datetime, format string) Example: DATETIME_FORMAT(NOW(), "YYYY-MM") ``` ```javascript DATETIME_PARSE() : Parses the datetime string with optional format and locale such as DATETIME_PARSE(Datetime, format, local) ``` ```javascript SET_LOCALE() : Sets a specific locale for the datetime, such as SET_LOCALE(Datetime) Example: SET_LOCALE(NOW(), "ru") ``` ```javascript SET_TIMEZONE() : Sets a specific timezone for a datetime such as SET_TIMEZONE(Datetime, timezone) Example: SET_TIMEZONE(NOW(), "Australia/Sydney") ``` ```javascript MONTH() : Returns the month part of the datetime such as MONTH(date) Example: MONTH({Completion date}) ``` ```javascript YEAR() : Returns the year part of the datetime such as YEAR(date) Example: YEAR({Completion date}) ``` ```javascript DAY() : Returns the day part of the datetime such as DAY(date) Example: DAY({Completion date}) ``` ```javascript TIMESTR() Formats a datetime into a time-only string (HH:mm:ss) such as TIMESTR(Datetime) Example: TIMESTR(NOW()) ``` ```javascript DATESTR() : Formats a datetime into a string (YYYY-MM-DD) such as DATESTR(Datetime) Example: DATESTR(NOW()) ``` ```javascript HOUR() : Returns the hour part of the datetime such as HOUR(date) Example: HOUR({Completion date}) ``` ```javascript MINUTE() : Returns the minute part of the datetime such as MINUTE(date) Example: MINUTE({Completion date}) ``` ```javascript SECOND() : Returns the second of the datetime such as SECOND(date) Example: SECOND({Completion date}) ``` ```javascript WEEKDAY() : Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday) Example: WEEKDAY({Date}) ``` ```javascript WORKDAY_DIFF() : Counts the number of working days between startDate and endDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates such as WORKDAY_DIFF(startDate, endDate, [holidays]) Example: WORKDAY_DIFF({Assignment date}, {Due Date}, "2019-09-04,2019-10-09,2019-11-10") ``` ```javascript ISAFTER() : Checks if date A is after B such as ISAFTER(A, B) Example : ISAFTER({Deadline},NOW()) ``` ```javascript ISBEFORE() : Checks if date A is earlier than B such as ISBEFORE(A, B) Example: ISBEFORE({Deadline}, NOW()) ``` ```javascript ISSAME() : Checks if the datetimes are the same up to some unit such as ISSAME(A, B, units) Example: ISSAME({Deadline}, NOW(), "hour") ``` ```javascript WEEKNUM(Date) : Returns the week number of the year. Example: WEEKNUM("04/01/2021") will return 1 ``` ```javascript WORKDAY(startDate,numDays,[holidays]) : Returns a date that is numDays working days after startDate. Working days exclude weekends and list of holidays formatted as comma-seperated string in ISO dates. Example: WORKDAY({Column_name},100,"2019-09-04,2019-10-02") ``` ### Array Functions ```javascript ARRAYJOIN() : Join the array of aggregation items into a string with a separator.  Example : ARRAYJOIN(Values) ``` ```javascript ARRAYUNIQUE() : Returns only unique items in the array.  Example : ARRAYUNIQUE(Values) ``` ### REGEX Formulas ```javascript REGEX_REPLACE() : The REGEX_REPLACE() function substitutes all matching substrings with a replacement string value.  Example : IF({REGEX MATCH}, UPPER(REGEX_REPLACE( {Phone}, ‘[^A-Za-z0-9]‘, ‘’)), ERROR(‘Invalid phone number’)) ``` ```javascript REGEX_MATCH() : The REGEX_MATCH() function returns whether the input text matches a regular expression and outputs in the Boolean data type as a 1 as true or 0 as false result.  Example : REGEX_MATCH({Email}, “(\\W|^)[\\w.\\-]{0,25}@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}(\\W|$)“) ``` ```javascript REGEX_EXTRACT() : In the REGEX_EXTRACT() function it returns the first substring that matches a regular expression.  Example :  REGEX_EXTRACT({URL}, “[^=]+$“) ``` #### Other Formulas ```javascript HYPERLINK() : Convert text into hyperlink text. Example: HYPERLINK("https://google.com","Google Search Engine") ``` ```javascript ENCODE_URL_COMPONENT() : Convert text into compatible URL, convert spaces into related character. Example: ENCODE_URL_COMPONENT("Best Search Engine") ``` ```javascript ROW_ID() : Return row id of current row. Example: ROW_ID() ```