--- title: Formula function reference | Lightfield description: Every operator and function you can use in a formula field, with what each one takes and returns. --- This page lists every operator and function the formula editor accepts. For how formulas treat blank values, currencies and related records, see [Formula fields](/help/settings/data-model/formula-fields/index.md). ## Who can use it Formula fields are on Enterprise, and only owners and admins write formulas. See [Formula fields](/help/settings/data-model/formula-fields/#who-can-use-it/index.md) for what other plans can do instead. Earlier plans can differ. [Check your workspace’s available features](/help/missing-feature/index.md). ## 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](/help/settings/data-model/formula-fields/#how-blank-values-work/index.md). ## 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 `if`s when you put records into bands. See the [tiers example](/help/settings/data-model/formula-fields/#example-sort-records-into-tiers/index.md). ## 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. | ## Functions that work on related records 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](/help/settings/data-model/formula-fields/#total-related-records-with-a-rollup/index.md). ## 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.