Write a formula
Updated
A Formula field works out its value from the record's other fields, such as a line total, a full name or the days left before a deadline. You write the formula once, and every record shows its own result, kept up to date as the fields it reads change.
Add a formula
- Press the + after the last field and choose Formula.
- Write the formula in Formula. Click a field's name under the box to insert it, or press Functions to see every function.
- Under Format the result as, keep As computed, or choose Number, Currency, Percent or Date to set decimals, a symbol or a date format.
- Press Create field.
If the formula has a mistake, the dialog says what and where, under the box, and does not save until it is fixed.
How formulas are written
- Fields go in braces:
{Unit price}. Always use braces, even for one-word names. - Text goes in quotes:
"Done"or'Done'. - Arithmetic:
+ - * /. Join text with&. - Compare with
= != < > <= >=. - An empty field counts as 0 in arithmetic. A division by zero gives an empty result.
Examples
| Formula | Gives |
|---|---|
{Quantity} * {Unit price} | A line total. |
ROUND({Quantity} * {Unit price} * 1.19, 2) | The total with 19% VAT, to two decimals. |
{First name} & " " & {Last name} | A full name. |
IF({Budget} > 2000, "Large", "Small") | A label from a number. |
DATETIME_DIFF({Due date}, TODAY(), "days") | Days until the deadline. |
IF(IS_BEFORE({Due date}, TODAY()), "Overdue", "On time") | Whether a task is late. |
DATEADD({Start}, 14, "days") | A date two weeks later. |
WORKDAY_DIFF({Start}, {End}) | Working days between two dates. |
DATETIME_FORMAT({Due date}, "D MMMM YYYY") | A date as text, such as 20 November 2026. |
SWITCH({Status}, "Done", 3, "In progress", 2, 1) | A number for each status. |
LOWER(TRIM({Email})) | A tidied email address. |
The functions
- Logic:
IF,SWITCH,AND,OR,NOT,XOR,BLANK,ERROR,ISERROR,RECORD_ID. - Text:
CONCATENATE,LEFT,RIGHT,MID,LEN,FIND,SEARCH,SUBSTITUTE,REPLACE,TRIM,UPPER,LOWER,REPT,T,VALUE,ENCODE_URL_COMPONENT,REGEX_MATCH,REGEX_EXTRACT,REGEX_REPLACE. - Numbers:
SUM,AVERAGE,MIN,MAX,COUNT,COUNTA,COUNTALL,ROUND,ROUNDUP,ROUNDDOWN,INT,CEILING,FLOOR,EVEN,ODD,ABS,MOD,POWER,SQRT,EXP,LOG. - Dates:
TODAY,NOW,YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEKDAY,WEEKNUM,DATEADD,DATETIME_DIFF,DATETIME_FORMAT,DATETIME_PARSE,DATESTR,TIMESTR,IS_BEFORE,IS_AFTER,IS_SAME,WORKDAY,WORKDAY_DIFF,TONOW,FROMNOW,SET_LOCALE,SET_TIMEZONE. - Lists, for lookups and multiple selects:
ARRAYJOIN,ARRAYUNIQUE,ARRAYCOMPACT,ARRAYFLATTEN,ARRAYSLICE.
Coming from Airtable
Formulas use Airtable's syntax and function names, so most Airtable formulas work unchanged. When you move a base from Airtable, each formula is translated, then worked out for every record and compared with the value Airtable had; the import report says per field whether it matches. CREATED_TIME() and LAST_MODIFIED_TIME() become Created time and Last modified time fields. A function Artavolo does not have is named in the report, and its cells show #ERROR until that part is rewritten.
Good to know
- Formulas are worked out on the server too, so the API, AI chat and agents read the values you see. "Today" on the server is the server's day, unless the request gives a time zone.
- A formula can read lookups, rollups, counts and other formulas, but not its own field.
- Formula values cannot be edited, and the API cannot filter or sort by them yet.
- Deleting a field a formula reads asks first, and the formula then shows
#ERROR. - Only the base's owners add and change formulas.