Formulas

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

ABS

Returns the absolute value of a number.

ABS(number)

Example

ABS(-5) → 5

CEILING

Rounds a number up to the nearest multiple of significance.

CEILING(number, [significance])

Example

CEILING(4.3, 1) → 5

FLOOR

Rounds a number down to the nearest multiple of significance.

FLOOR(number, [significance])

Example

FLOOR(4.7, 1) → 4

INT

Returns the integer portion of a number by rounding down.

INT(number)

Example

INT(8.9) → 8

MOD

Returns the remainder after division.

MOD(number, divisor)

Example

MOD(10, 3) → 1

POWER

Returns the result of a number raised to a power.

POWER(base, exponent)

Example

POWER(2, 3) → 8

ROUND

Rounds a number to a specified number of decimal places.

ROUND(number, [decimals])

Example

ROUND(3.14159, 2) → 3.14

SQRT

Returns the square root of a number.

SQRT(number)

Example

SQRT(16) → 4

SUM

Returns the sum of the numbers.

SUM(number1, [number2, ...])

Example

SUM({Savings}, {Checking})

Text Functions

CONCATENATE

Joins several text strings into one.

CONCATENATE(text1, [text2, ...])

Example

CONCATENATE({FirstName}, " ", {LastName})

FIND

Returns the position of text within text (case-sensitive).

FIND(find_text, within_text, [start_num])

Example

FIND("Day", "Monday") → 4

LEFT

Returns the leftmost characters from a text string.

LEFT(text, [num_chars])

Example

LEFT("Hello World", 5) → "Hello"

LEN

Returns the length of a text string.

LEN(text)

Example

LEN("Hello") → 5

LOWER

Converts text to lowercase.

LOWER(text)

Example

LOWER("HELLO") → "hello"

MID

Returns a substring from the middle of text.

MID(text, start_num, num_chars)

Example

MID("Hello World", 7, 5) → "World"

PROPER

Capitalizes the first letter of each word.

PROPER(text)

Example

PROPER("hello world") → "Hello World"

REPT

Repeats text a given number of times.

REPT(text, number_times)

Example

REPT("*", 5) → "*****"

RIGHT

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

SUBSTITUTE

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"

TEXT

Formats a number as text with a specified format.

TEXT(value, format_text)

Example

TEXT(1234.5, "$#,##0.00")

TRIM

Removes leading and trailing spaces from text.

TRIM(text)

Example

TRIM("  Hello  ") → "Hello"

UPPER

Converts text to uppercase.

UPPER(text)

Example

UPPER("hello") → "HELLO"

VALUE

Converts text to a number.

VALUE(text)

Example

VALUE("$1,234.50") → 1234.5

Statistical Functions

AVERAGE

Returns the average of the numbers.

AVERAGE(number1, [number2, ...])

Example

AVERAGE(10, 20, 30) → 20

COUNT

Counts the number of non-empty values.

COUNT(value1, [value2, ...])

Example

COUNT({Tasks.Status}) → 5

MAX

Returns the maximum value.

MAX(number1, [number2, ...])

Example

MAX({Sales.Amount}) → 10000

MIN

Returns the minimum value.

MIN(number1, [number2, ...])

Example

MIN({Prices.Cost}) → 9.99

Date Functions

DATEADD

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")

DATETIME_DIFF

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")

DATETIME_FORMAT

Formats a date/time using a format string.

DATETIME_FORMAT(date, format_string)

Example

DATETIME_FORMAT({Date}, "YYYY-MM-DD")

DATESTR

Returns date as YYYY-MM-DD string.

DATESTR(date)

Example

DATESTR({Date}) → "2024-07-15"

TIMESTR

Returns time as HH:MM:SS string.

TIMESTR(datetime)

Example

TIMESTR({Timestamp}) → "14:30:45"

DAY

Returns the day of the month (1-31).

DAY(date)

Example

DAY({BirthDate}) → 15

MONTH

Returns the month (1-12).

MONTH(date)

Example

MONTH({Date}) → 7

YEAR

Returns the year.

YEAR(date)

Example

YEAR({Date}) → 2024

HOUR

Returns the hour component (0-23).

HOUR(datetime)

Example

HOUR({Timestamp}) → 14

MINUTE

Returns the minute component (0-59).

MINUTE(datetime)

Example

MINUTE({Timestamp}) → 30

SECOND

Returns the second component (0-59).

SECOND(datetime)

Example

SECOND({Timestamp}) → 45

WEEKDAY

Returns the day of week as a number.

WEEKDAY(date, [return_type])

Example

WEEKDAY({Date}) → 2

WEEKNUM

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")

IFS

Checks multiple conditions and returns the value for the first TRUE.

IFS(condition1, value1, [condition2, value2, ...])

Example

IFS({Score} >= 90, "A", {Score} >= 80, "B")

SWITCH

Evaluates an expression against a list of values.

SWITCH(expression, value1, result1, [value2, result2, ...], [default])

Example

SWITCH({Status}, "new", "🔵", "done", "✅")

AND

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})

NOT

Reverses the logic of its argument.

NOT(logical)

Example

NOT({IsComplete})

XOR

Returns TRUE if an odd number of arguments are TRUE.

XOR(logical1, [logical2, ...])

Example

XOR({OptionA}, {OptionB})

IFERROR

Returns value unless it’s an error.

IFERROR(value, value_if_error)

Example

IFERROR(1/{Count}, 0)

IFNA

Returns value unless it’s #N/A.

IFNA(value, value_if_na)

Example

IFNA({LookupResult}, "Not Found")

BLANK

Returns an empty/blank value.

BLANK()

Example

IF({Score} < 50, BLANK(), {Score})

TRUE

Returns the boolean value TRUE.

TRUE()

Example

IF({Count} > 0, TRUE(), FALSE())

FALSE

Returns the boolean value FALSE.

FALSE()

Example

IF({Count} = 0, FALSE(), TRUE())

Information Functions

ISBLANK

Returns TRUE if value is blank, empty, or null.

ISBLANK(value)

Example

IF(ISBLANK({Email}), "No email", {Email})

ISERROR

Returns TRUE if value is any error.

ISERROR(value)

Example

IF(ISERROR({Calculation}), 0, {Calculation})

ISNUMBER

Returns TRUE if value is a number.

ISNUMBER(value)

Example

IF(ISNUMBER({Input}), {Input} * 2, 0)

ISTEXT

Returns TRUE if value is text.

ISTEXT(value)

Example

IF(ISTEXT({Input}), UPPER({Input}), "")

COALESCE

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())

RECORD_ID

Returns the ID of the current record.

RECORD_ID()

Example

CONCATENATE("ID-", RECORD_ID())

CREATED_TIME

Returns the creation time of the current record.

CREATED_TIME()

Example

DATESTR(CREATED_TIME())

LAST_MODIFIED_TIME

Returns the last modification time of the current record.

LAST_MODIFIED_TIME()

Example

DATETIME_DIFF(LAST_MODIFIED_TIME(), CREATED_TIME(), "day")

Array Functions

ARRAYJOIN

Joins array elements into a string with optional separator.

ARRAYJOIN(array, [separator])

Example

ARRAYJOIN({Tags}, ", ")

ARRAYCOMPACT

Removes empty/null values from an array.

ARRAYCOMPACT(array)

Example

ARRAYCOMPACT({Responses})

ARRAYUNIQUE

Returns unique values from an array.

ARRAYUNIQUE(array)

Example

ARRAYUNIQUE({Categories})

UNIQUE

Alias for ARRAYUNIQUE.

UNIQUE(array)

Example

UNIQUE({Tags})

ARRAYFLATTEN

Flattens nested arrays into a single-level array.

ARRAYFLATTEN(array)

Example

ARRAYFLATTEN([[1, 2], [3, 4]])

ARRAYSLICE

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)

FILTER

Filters array elements based on a condition.

FILTER(array, condition)

Example

FILTER({Numbers}, "> 10")

SORT

Sorts array elements.

SORT(array, [descending])

Example

SORT({Scores}, TRUE)

Regex Functions

REGEX_MATCH

Tests if text matches a regex pattern.

REGEX_MATCH(text, pattern, [flags])

Example

REGEX_MATCH({Email}, ".+@.+\\..+")

REGEX_EXTRACT

Extracts the first match of a regex pattern.

REGEX_EXTRACT(text, pattern, [flags])

Example

REGEX_EXTRACT({Email}, "([^@]+)")

REGEX_EXTRACT_ALL

Extracts all matches as an array.

REGEX_EXTRACT_ALL(text, pattern, [flags])

Example

REGEX_EXTRACT_ALL({Text}, "#\\w+")

REGEX_REPLACE

Replaces all matches of a regex pattern.

REGEX_REPLACE(text, pattern, replacement, [flags])

Example

REGEX_REPLACE({Phone}, "[^0-9]", "")

REGEX_COUNT

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}, "@(.+)$")

Getting Started

Configure Database

Manage Data

Access & Sharing

Automations

Tips

Zite MCP

API Reference

Syntax

Math Functions

Text Functions

Statistical Functions

Date Functions

Logical Functions

Information Functions

Array Functions

Regex Functions

Common Use Cases

Days since record was created

Full name from first and last

Status badge with emoji

Days between two dates

Percentage complete

Extract domain from email

​Syntax

​Math Functions

​Text Functions

​Statistical Functions

​Date Functions

​Logical Functions

​Information Functions

​Array Functions

​Regex Functions

​Common Use Cases

​Days since record was created

​Full name from first and last

​Status badge with emoji

​Days between two dates

​Percentage complete

​Extract domain from email

Syntax

Math Functions

Text Functions

Statistical Functions

Date Functions

Logical Functions

Information Functions

Array Functions

Regex Functions

Common Use Cases

Days since record was created

Full name from first and last

Status badge with emoji

Days between two dates

Percentage complete

Extract domain from email