Formula function reference

Records and data

Every operator and function you can use in a formula field, with what each one takes and returns.

Available on Enterprise

This page lists every operator and function the formula editor accepts. For how formulas treat blank values, currencies and related records, see Formula fields.

Who can use it

Formula fields are on Enterprise, and only owners and admins write formulas. See Formula fields for what other plans can do instead.

Earlier plans can differ. Check your workspace’s available features.

Find a function in the app

In the formula editor, click Variables and search the Functions section. Each function shows a short description and a worked example.

In the tables below, ? marks an optional argument and ... means you can pass as many values as you like.

Operators

Operator	Meaning	Same as
`+` `-` `*` `/`	Add, subtract, multiply, divide	`add`, `sub`, `mul`, `div`
`==` `!=`	Equal to, not equal to	`eq`, `neq`
`>` `>=` `<` `<=`	Greater than, greater than or equal to, less than, less than or equal to	`gt`, `gte`, `lt`, `lte`
`&&` `\|\|` `!`	And, or, not	`and`, `or`, `not`
`a ? b : c`	If `a` is true, `b`; otherwise `c`	`if(a, b, c)`

Use parentheses to choose which calculation runs first: ({ARR} - {Cost}) / {ARR}. A minus sign in front of a value makes it negative: -{Discount}.

Math

Function	What it does
`add(a, b)`	Adds two numbers. Same as `a + b`.
`sub(a, b)`	Subtracts `b` from `a`. Same as `a - b`.
`mul(a, b)`	Multiplies two numbers. Same as `a * b`.
`div(a, b)`	Divides `a` by `b`. Same as `a / b`. Dividing by zero gives a blank.
`sum(a, ...)`	Adds any number of values together, or totals a field across related records.
`avg(a, ...)`	The average of the values, or of a field across related records.
`count(relatedValues)`	How many related records there are, or how many have a value in a field. Works only on related records.

Rounding

Function	What it does
`round(value, decimalPlaces?)`	Rounds to the nearest whole number, or to `decimalPlaces` decimal places. `round(1.239, 2)` is `1.24`.
`floor(value)`	Rounds down to a whole number.
`ceil(value)`	Rounds up to a whole number.
`abs(value)`	The value without its sign.

Rounding a currency amount keeps its currency.

Bounds

Function	What it does
`min(a, ...)`	The smallest number or earliest date, from the values you pass or across related records.
`max(a, ...)`	The largest number or latest date, from the values you pass or across related records.
`clamp(value, lowerBound, upperBound)`	Keeps `value` between the two bounds. `clamp(12, 1, 10)` is `10`.

min and max compare numbers with numbers or dates with dates, never a mix of the two.

Comparison

Function	What it does
`eq(a, b)`	True when the values are equal. Same as `a == b`.
`neq(a, b)`	True when the values differ. Same as `a != b`.
`gt(a, b)` / `gte(a, b)`	True when `a` is greater than (or equal to) `b`. Numbers, currency and dates.
`lt(a, b)` / `lte(a, b)`	True when `a` is less than (or equal to) `b`. Numbers, currency and dates.

eq and neq work on any type. The four ordering comparisons work on numbers, currency and dates, and both sides must be the same kind; you can’t use > to compare text, or a date with a number.

For what a comparison with a blank value returns, see How blank values work.

Logic and conditions

Function	What it does
`and(a, ...)`	True when every value is true. Same as `a && b`.
`or(a, ...)`	True when any value is true. Same as `a \|\| b`.
`not(value)`	Turns true into false and false into true. Same as `!value`.
`if(condition, whenTrue, whenFalse)`	Picks one of two values based on a condition.
`case(condition1, result1, ..., default?)`	Returns the result for the first condition that’s true. The last default is optional.

Only the branch a condition picks is calculated, so you can guard a division: if({Total} != 0, {Won} / {Total}, 0).

case is easier to read than nested ifs when you put records into bands. See the tiers example.

Text

Function	What it does
`concat(a, ...)`	Joins text values, including text from related records, with no separator.
`join(relatedText, delimiter)`	Joins text from related records with a separator. `join({Opportunities.Stage.label}, ", ")` gives something like `"Open, Won"`.
`upper(text)` / `lower(text)`	Converts text to upper or lower case.
`trim(text)`	Removes spaces at the start and end.
`len(text)`	The number of characters in the text.
`left(text, count)` / `right(text, count)`	The first or last `count` characters.
`mid(text, start, count)`	`count` characters starting at position `start` (the first character is 1).
`replace(text, search, replacement)`	Replaces every occurrence of `search`. `replace("555-1234", "-", "")` is `"5551234"`.
`contains(text, search)`	True when the text contains `search`.
`find(text, search)`	The position of the first match (starting at 1), or 0 when there’s no match.
`split(text, delimiter, index)`	Splits the text on `delimiter` and returns piece number `index` (starting at 1). `split("jane@acme.com", "@", 2)` is `"acme.com"`.

Text functions accept text only. There’s no way to turn a number into text, so you can’t fold a number into a text label.

Dates

Function	What it does
`dateDiff(startDate, endDate, unit)`	The number of whole units from `startDate` to `endDate`. Negative when `endDate` is earlier.
`dateAdd(date, amount, unit)`	Moves a date by `amount` units. A negative `amount` moves it earlier.
`year(date)`	The four-digit year.
`quarter(date)`	The calendar quarter, 1 to 4.
`month(date)`	The month, 1 to 12.
`day(date)`	The day of the month, 1 to 31.

The unit must be written in quotes, as one of "seconds", "minutes", "hours", "days", "weeks", "months" or "years". For example, dateDiff({Created at}, {Close date}, "days").

year, quarter, month and day read the date in UTC. There’s no function for the current date or time.

Currency

Function	What it does
`convertCurrency(value, "USD", rate)`	Converts an amount into another currency at the rate you give.

This is the only way to combine amounts held in different currencies. Write the currency as a quoted ISO 4217 code, such as "USD" or "EUR". For example, using an illustrative rate of 1.1476, convertCurrency({Amount (EUR)}, "USD", 1.1476) turns €100 into $114.76. Supply the rate you want to use; this example is not a live exchange rate.

Blank values

Function	What it does
`isBlank(value)`	True when the value is empty or not set. Empty text counts as blank; `0` and an unchecked checkbox don’t.
`isSet(value)`	True when the value is present.
`coalesce(a, ...)`	The first value that’s set, from the values you pass or across related records. `coalesce({Q3}, 0)` treats a blank as zero.

These functions can summarize a field across every related record (a rollup): sum, avg, count, min, max, concat, join and coalesce. count and join work only on related records. For how rollups handle blanks and which relationships they can use, see Total related records with a rollup.

Common questions

Is there a function for today’s date? No. Formulas only read values stored on records. To measure time between two stored dates, use dateDiff, for example dateDiff({Created at}, {Close date}, "days").

How do I convert between currencies? Use convertCurrency with the rate you choose, for example convertCurrency({Amount (EUR)}, "USD", 1.1476). Lightfield never converts on its own.

Can I turn a number into text? No. Text functions accept text only, so you can’t fold a number into a text label. Use a separate Number or Currency formula field for the value.