> ## Documentation Index > Fetch the complete documentation index at: https://zite.com/llms.txt > Use this file to discover all available pages before exploring further. # Formulas > Complete reference for all formula functions available in formula fields. Formula fields let you compute values dynamically based on other fields in your records. Use formulas for calculations, text manipulation, date operations, and conditional logic. ## Syntax * **Field references**: Use curly braces to reference other fields: `{FieldName}` * **Linked record fields**: Access fields from linked records using dot notation: `{Tasks.Status}` * **Text strings**: Wrap text in double quotes: `"Hello World"` * **Numbers**: Use plain numbers: `42`, `3.14` * **Operators**: Standard math operators work: `+`, `-`, `*`, `/` * **Comparisons**: `=`, `!=`, `<`, `>`, `<=`, `>=` *** ## Math Functions Returns the absolute value of a number. ``` ABS(number) ``` **Example** ``` ABS(-5) → 5 ``` Rounds a number up to the nearest multiple of significance. ``` CEILING(number, [significance]) ``` **Example** ``` CEILING(4.3, 1) → 5 ``` Rounds a number down to the nearest multiple of significance. ``` FLOOR(number, [significance]) ``` **Example** ``` FLOOR(4.7, 1) → 4 ``` Returns the integer portion of a number by rounding down. ``` INT(number) ``` **Example** ``` INT(8.9) → 8 ``` Returns the remainder after division. ``` MOD(number, divisor) ``` **Example** ``` MOD(10, 3) → 1 ``` Returns the result of a number raised to a power. ``` POWER(base, exponent) ``` **Example** ``` POWER(2, 3) → 8 ``` Rounds a number to a specified number of decimal places. ``` ROUND(number, [decimals]) ``` **Example** ``` ROUND(3.14159, 2) → 3.14 ``` Returns the square root of a number. ``` SQRT(number) ``` **Example** ``` SQRT(16) → 4 ``` Returns the sum of the numbers. ``` SUM(number1, [number2, ...]) ``` **Example** ``` SUM({Savings}, {Checking}) ``` *** ## Text Functions Joins several text strings into one. ``` CONCATENATE(text1, [text2, ...]) ``` **Example** ``` CONCATENATE({FirstName}, " ", {LastName}) ``` Returns the position of text within text (case-sensitive). ``` FIND(find_text, within_text, [start_num]) ``` **Example** ``` FIND("Day", "Monday") → 4 ``` Returns the leftmost characters from a text string. ``` LEFT(text, [num_chars]) ``` **Example** ``` LEFT("Hello World", 5) → "Hello" ``` Returns the length of a text string. ``` LEN(text) ``` **Example** ``` LEN("Hello") → 5 ``` Converts text to lowercase. ``` LOWER(text) ``` **Example** ``` LOWER("HELLO") → "hello" ``` Returns a substring from the middle of text. ``` MID(text, start_num, num_chars) ``` **Example** ``` MID("Hello World", 7, 5) → "World" ``` Capitalizes the first letter of each word. ``` PROPER(text) ``` **Example** ``` PROPER("hello world") → "Hello World" ``` Repeats text a given number of times. ``` REPT(text, number_times) ``` **Example** ``` REPT("*", 5) → "*****" ``` Returns the rightmost characters from a text string. ``` RIGHT(text, [num_chars]) ``` **Example** ``` RIGHT("Hello World", 5) → "World" ``` Returns the position of text within text (case-insensitive). ``` SEARCH(find_text, within_text, [start_num]) ``` **Example** ``` SEARCH("day", "Monday") → 4 ``` Replaces old text with new text in a string. ``` SUBSTITUTE(text, old_text, new_text, [instance_num]) ``` **Example** ``` SUBSTITUTE("Hello World", "World", "There") ``` Returns the text if value is text, otherwise returns empty string. ``` T(value) ``` **Example** ``` T("Hello") → "Hello" ``` Formats a number as text with a specified format. ``` TEXT(value, format_text) ``` **Example** ``` TEXT(1234.5, "$#,##0.00") ``` Removes leading and trailing spaces from text. ``` TRIM(text) ``` **Example** ``` TRIM(" Hello ") → "Hello" ``` Converts text to uppercase. ``` UPPER(text) ``` **Example** ``` UPPER("hello") → "HELLO" ``` Converts text to a number. ``` VALUE(text) ``` **Example** ``` VALUE("$1,234.50") → 1234.5 ``` *** ## Statistical Functions Returns the average of the numbers. ``` AVERAGE(number1, [number2, ...]) ``` **Example** ``` AVERAGE(10, 20, 30) → 20 ``` Counts the number of non-empty values. ``` COUNT(value1, [value2, ...]) ``` **Example** ``` COUNT({Tasks.Status}) → 5 ``` Returns the maximum value. ``` MAX(number1, [number2, ...]) ``` **Example** ``` MAX({Sales.Amount}) → 10000 ``` Returns the minimum value. ``` MIN(number1, [number2, ...]) ``` **Example** ``` MIN({Prices.Cost}) → 9.99 ``` *** ## Date Functions Adds a number of units to a date. ``` DATEADD(date, count, unit) ``` Units: `"year"`, `"month"`, `"week"`, `"day"`, `"hour"`, `"minute"`, `"second"` **Example** ``` DATEADD({StartDate}, 7, "day") ``` Returns the difference between two dates in specified units. ``` DATETIME_DIFF(date1, date2, unit) ``` Units: `"year"`, `"month"`, `"week"`, `"day"`, `"hour"`, `"minute"`, `"second"` **Example** ``` DATETIME_DIFF({StartDate}, {EndDate}, "day") ``` Formats a date/time using a format string. ``` DATETIME_FORMAT(date, format_string) ``` **Example** ``` DATETIME_FORMAT({Date}, "YYYY-MM-DD") ``` Returns date as YYYY-MM-DD string. ``` DATESTR(date) ``` **Example** ``` DATESTR({Date}) → "2024-07-15" ``` Returns time as HH:MM:SS string. ``` TIMESTR(datetime) ``` **Example** ``` TIMESTR({Timestamp}) → "14:30:45" ``` Returns the day of the month (1-31). ``` DAY(date) ``` **Example** ``` DAY({BirthDate}) → 15 ``` Returns the month (1-12). ``` MONTH(date) ``` **Example** ``` MONTH({Date}) → 7 ``` Returns the year. ``` YEAR(date) ``` **Example** ``` YEAR({Date}) → 2024 ``` Returns the hour component (0-23). ``` HOUR(datetime) ``` **Example** ``` HOUR({Timestamp}) → 14 ``` Returns the minute component (0-59). ``` MINUTE(datetime) ``` **Example** ``` MINUTE({Timestamp}) → 30 ``` Returns the second component (0-59). ``` SECOND(datetime) ``` **Example** ``` SECOND({Timestamp}) → 45 ``` Returns the day of week as a number. ``` WEEKDAY(date, [return_type]) ``` **Example** ``` WEEKDAY({Date}) → 2 ``` Returns the week number of the year. ``` WEEKNUM(date, [return_type]) ``` **Example** ``` WEEKNUM({Date}) → 23 ``` *** ## Logical Functions Returns one value if condition is TRUE, another if FALSE. ``` IF(logical_test, value_if_true, [value_if_false]) ``` **Example** ``` IF({Score} >= 90, "A", "B") ``` Checks multiple conditions and returns the value for the first TRUE. ``` IFS(condition1, value1, [condition2, value2, ...]) ``` **Example** ``` IFS({Score} >= 90, "A", {Score} >= 80, "B") ``` Evaluates an expression against a list of values. ``` SWITCH(expression, value1, result1, [value2, result2, ...], [default]) ``` **Example** ``` SWITCH({Status}, "new", "🔵", "done", "✅") ``` Returns TRUE if all arguments are TRUE. ``` AND(logical1, [logical2, ...]) ``` **Example** ``` AND({Active}, {Approved}) ``` Returns TRUE if any argument is TRUE. ``` OR(logical1, [logical2, ...]) ``` **Example** ``` OR({IsUrgent}, {IsImportant}) ``` Reverses the logic of its argument. ``` NOT(logical) ``` **Example** ``` NOT({IsComplete}) ``` Returns TRUE if an odd number of arguments are TRUE. ``` XOR(logical1, [logical2, ...]) ``` **Example** ``` XOR({OptionA}, {OptionB}) ``` Returns value unless it's an error. ``` IFERROR(value, value_if_error) ``` **Example** ``` IFERROR(1/{Count}, 0) ``` Returns value unless it's #N/A. ``` IFNA(value, value_if_na) ``` **Example** ``` IFNA({LookupResult}, "Not Found") ``` Returns an empty/blank value. ``` BLANK() ``` **Example** ``` IF({Score} < 50, BLANK(), {Score}) ``` Returns the boolean value TRUE. ``` TRUE() ``` **Example** ``` IF({Count} > 0, TRUE(), FALSE()) ``` Returns the boolean value FALSE. ``` FALSE() ``` **Example** ``` IF({Count} = 0, FALSE(), TRUE()) ``` *** ## Information Functions Returns TRUE if value is blank, empty, or null. ``` ISBLANK(value) ``` **Example** ``` IF(ISBLANK({Email}), "No email", {Email}) ``` Returns TRUE if value is any error. ``` ISERROR(value) ``` **Example** ``` IF(ISERROR({Calculation}), 0, {Calculation}) ``` Returns TRUE if value is a number. ``` ISNUMBER(value) ``` **Example** ``` IF(ISNUMBER({Input}), {Input} * 2, 0) ``` Returns TRUE if value is text. ``` ISTEXT(value) ``` **Example** ``` IF(ISTEXT({Input}), UPPER({Input}), "") ``` Returns the first non-null, non-empty value. ``` COALESCE(value1, [value2, ...]) ``` **Example** ``` COALESCE({Nickname}, {FirstName}, "Guest") ``` Converts value to a number. ``` N(value) ``` **Example** ``` N({TextOrNumber}) + 10 ``` Returns the #N/A error value. ``` NA() ``` **Example** ``` IF({Valid}, {Value}, NA()) ``` Returns the ID of the current record. ``` RECORD_ID() ``` **Example** ``` CONCATENATE("ID-", RECORD_ID()) ``` Returns the creation time of the current record. ``` CREATED_TIME() ``` **Example** ``` DATESTR(CREATED_TIME()) ``` Returns the last modification time of the current record. ``` LAST_MODIFIED_TIME() ``` **Example** ``` DATETIME_DIFF(LAST_MODIFIED_TIME(), CREATED_TIME(), "day") ``` *** ## Array Functions Joins array elements into a string with optional separator. ``` ARRAYJOIN(array, [separator]) ``` **Example** ``` ARRAYJOIN({Tags}, ", ") ``` Removes empty/null values from an array. ``` ARRAYCOMPACT(array) ``` **Example** ``` ARRAYCOMPACT({Responses}) ``` Returns unique values from an array. ``` ARRAYUNIQUE(array) ``` **Example** ``` ARRAYUNIQUE({Categories}) ``` Alias for ARRAYUNIQUE. ``` UNIQUE(array) ``` **Example** ``` UNIQUE({Tags}) ``` Flattens nested arrays into a single-level array. ``` ARRAYFLATTEN(array) ``` **Example** ``` ARRAYFLATTEN([[1, 2], [3, 4]]) ``` Extracts a portion of an array from start to end index (1-based). Supports negative indices (-1 for last element). ``` ARRAYSLICE(array, start, [end]) ``` **Example** ``` ARRAYSLICE({Items}, 1, 3) ``` Filters array elements based on a condition. ``` FILTER(array, condition) ``` **Example** ``` FILTER({Numbers}, "> 10") ``` Sorts array elements. ``` SORT(array, [descending]) ``` **Example** ``` SORT({Scores}, TRUE) ``` *** ## Regex Functions Tests if text matches a regex pattern. ``` REGEX_MATCH(text, pattern, [flags]) ``` **Example** ``` REGEX_MATCH({Email}, ".+@.+\\..+") ``` Extracts the first match of a regex pattern. ``` REGEX_EXTRACT(text, pattern, [flags]) ``` **Example** ``` REGEX_EXTRACT({Email}, "([^@]+)") ``` Extracts all matches as an array. ``` REGEX_EXTRACT_ALL(text, pattern, [flags]) ``` **Example** ``` REGEX_EXTRACT_ALL({Text}, "#\\w+") ``` Replaces all matches of a regex pattern. ``` REGEX_REPLACE(text, pattern, replacement, [flags]) ``` **Example** ``` REGEX_REPLACE({Phone}, "[^0-9]", "") ``` Counts the number of matches. ``` REGEX_COUNT(text, pattern, [flags]) ``` **Example** ``` REGEX_COUNT({Text}, "\\bword\\b") ``` *** ## Common Use Cases ### Days since record was created ``` DATETIME_DIFF(LAST_MODIFIED_TIME(), CREATED_TIME(), "day") ``` ### Full name from first and last ``` CONCATENATE({FirstName}, " ", {LastName}) ``` ### Status badge with emoji ``` SWITCH({Status}, "pending", "🟡 Pending", "approved", "✅ Approved", "rejected", "❌ Rejected", "Unknown" ) ``` ### Days between two dates ``` DATETIME_DIFF({StartDate}, {EndDate}, "day") ``` ### Percentage complete ``` ROUND({CompletedTasks} / {TotalTasks} * 100, 1) ``` ### Extract domain from email ``` REGEX_EXTRACT({Email}, "@(.+)$") ```