Formula field reference

04 Nov 2022
14 Minutes to read

Print
Dark
Light

Formula field reference

Updated on 04 Nov 2022
14 Minutes to read

Print
Dark
Light

Article Summary

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields.

Introduction

In a formula, you can reference fields (columns) by name. To return the value of an existing field you can simply input the name of that field:

Price

So for example, if you wanted a formula that calculated a total based on your Price and Quantity fields, that would look something like:

Price * Quantity

Field names with multiple words must be wrapped in curly braces:

MIN({Regular Price}, {Sale Price})

Formulas may include parentheses () to change the order of operations:

(Apples + Oranges) / Guests

Expressions

An expression is created when you combine values, fields, or formulas together and that combination evaluates to a single value. You can create simple expressions, like in the below example (the formula below would return a single value—so it's an expression):

{Sales Tax} * {Price}

You can also chain expressions together, this is especially helpful when using logical operators. For example, the technical pattern for the IF() formula is IF(expression, value1, value2). Taking our above example a step further, let's say we want to know when the total price of our item is over 100:

IF(
  {Sales Tax} * {Price} > 100,
  "This is over 100",
  "This is less than 100"
)

The expression in the above block is {Sales Tax} * {Price} > 100.

Your expressions can be as complicated as you'd like, or need them, to be! Want to play around with, and learn more about, expressions? Or formulas in general?

We have created the below Formula Playground base as a space to reference and test any of the formulas below. As the name of the base indicates—we created this Formula Playground base experience with "play" in mind for a more hands-on approach to learning and testing out formulas. We recommend creating a personal copy of the below Formula Playground base so you can easily reference it whenever you please.

Last Updated: 9/21/21

If this is your first time opening this base, check out this quick demo video for an overview of how this base is structured.

Text operators and functions

To see the below text functions in action, and test them out yourself, check out the "✏️Text function examples" table in the Formula Playground.

Text operators

Operator Description Examples

Operator	Description	Examples
`&`	Concatenate text values into a single text value. To concatenate static text, surround it with double quotation marks. To concatenate double quotation marks, you need to use a backslash (\) as an escape character. Equivalent to CONCATENATE().	`Name & " - " & Age` => Jane - 28 `"\""&{Product Name}&"\""` => "GreatProduct"

&

Concatenate text values into a single text value.

To concatenate static text, surround it with double quotation marks. To concatenate double quotation marks, you need to use a backslash (\) as an escape character.

Equivalent to CONCATENATE().

Name & " - " & Age

=> Jane - 28

"\""&{Product Name}&"\""

=> "GreatProduct"

Text functions

Function	Description	Examples
`ARRAYJOIN([item1, item2, item3], separator)`	Join the array of items into a string with a separator.	`ARRAYJOIN(values, "; ")` => 1; 2; 3
`CONCATENATE(text1, [text2, ...])`	Joins together the text arguments into a single text value. To concatenate static text, surround it with double quotation marks. To concatenate double quotation marks, you need to use a backslash (\) as an escape character. Equivalent to use of the & operator.	`CONCATENATE("Bob"," - ", 43)` => Bob - 43 `CONCATENATE("\"",{Name},"\"")` => "Bob"
`ENCODE_URL_COMPONENT(component_string)`	Replaces certain characters with encoded equivalents for use in constructing URLs or URIs. Does not encode the following characters: - _ . ~	`ENCODE_URL_COMPONENT("chicken & waffles")` => chicken%20%26%20waffles
`FIND(stringToFind, whereToSearch,[startFromPosition])`	Finds an occurrence of stringToFind in whereToSearch string starting from an optional startFromPosition.(startFromPosition is 0 by default.) If no occurrence of stringToFind is found, the result will be 0. Similar to SEARCH(), though SEARCH() returns empty rather than 0 if no occurrence of stringToFind is found.	`FIND("fox", "quick brown fox")` => 13
`LEFT(string, howMany)`	Extract howMany characters from the beginning of the string.	`LEFT("quick brown fox", 5)` => quick
`LEN(string)`	Returns the length of a string.	`LEN("quick brown fox")` => 15
`LOWER(string)`	Makes a string lowercase.	`LOWER("Hello!")` => hello!
`MID(string, whereToStart, count)`	Extract a substring of count characters starting at whereToStart.	`MID("quick brown fox", 6, 5)` => brown
`REPLACE(string, start_character, number_of_characters, replacement)`	Replaces the number of characters beginning with the start character with the replacement text. (If you're looking for a way to find and replace all occurrences of old_text with new_text, see SUBSTITUTE().)	`REPLACE("database", 2, 5, "o")` => dose
`REPT(string, number)`	Repeats string by the specified number of times.	`REPT("Hi! ", 3)` => Hi! Hi! Hi!
`RIGHT(string, howMany)`	Extract howMany characters from the end of the string.	`RIGHT("quick brown fox", 5)` => n fox
`SEARCH(stringToFind, whereToSearch,[startFromPosition])`	Searches for an occurrence of stringToFind in whereToSearch string starting from an optional startFromPosition. (startFromPosition is 0 by default.) If no occurrence of stringToFind is found, the result will be empty. Similar to FIND(), though FIND() returns 0 rather than empty if no occurrence of stringToFind is found.	`SEARCH("World", "Hello World")` => 7
`SUBSTITUTE(string, old_text, new_text, [index])`	Replaces occurrences of old_text with new_text. You can optionally specify an index number (starting from 1) to replace just a specific occurrence of old_text. If no index number is specified, then all occurrences of old_text will be replaced. (If you're looking for a way to replace characters in a string from a specified start point instead, see REPLACE().) Looking for examples of how you can use SUBSTITUTE()? Check out this blog post on 7 time-saving substitution formulas.	`SUBSTITUTE("gold mold", "old", "et")` => get met `SUBSTITUTE("gold mold", "old", "et", 1)` => get mold
`T(value1)`	Returns the argument if it is text and blank otherwise.	`T("text only")` => text only `T(42)` => blank
`TRIM(string)`	Removes whitespace at the beginning and end of string.	`TRIM(" Hello! ")` => Hello!
`UPPER(string)`	Makes string uppercase.	`UPPER("Hello!")` => HELLO!

Logical operators and functions

To see the below logical functions in action, and test them out yourself, check out the "❓ Logical function examples" table in the Formula Playground.

Logical operators

Operator	Description	Examples
`>`	Greater than	`3 > 2` => TRUE
`<`	Less than	`2 < 3` => TRUE
`>=`	Greater than or equal to	`3 >= 3` => TRUE
`<=`	Less than or equal to	`2 <= 2` => TRUE
`=`	Equal to	`2 = 2` => TRUE
`!=`	Is not equal to	`3 != 2` => TRUE

Logical functions

Function	Description	Examples
`AND(expression, [expression, ...])`	Returns true if all the arguments are true, returns false otherwise.	`AND({Field 1}, {Field 2})` => 1 (if both field values are truthy)
`BLANK()`	Returns a blank value.	`IF({Price} > 1000000, "Wow, that's pretty expensive", BLANK())`
`ERROR()`	Returns the error value.	`IF({Price} < 0, ERROR(), "More than zero!")`
`FALSE()`	Logical value false. False is represented numerically by a 0.	`IF(2 = 3, TRUE(), FALSE())` => 0
`IF(expression, value1, value2)`	Returns value1 if the logical expression is true, otherwise it returns value2. Can also be used to make nested IF statements. Can also be used to check if a cell is blank/is empty.	`IF({Sales} > 50, "Win", "Lose")` `IF({WaterTemp} > 100, IF({WaterTemp }< 212, "just right", "too hot"), "too cold")` `IF({Due Date} = BLANK(), "Please enter date", "Date entered")`
`ISERROR(expr)`	Returns true if the expression causes an error.	`ISERROR(2/0)` => 1 (true because of the "divide by zero" error)
`NOT(expression)`	Reverses the logical value of its argument.	`NOT({Total} = 0)`
`OR(expression, [exp2, ...])`	Returns true if any one of the arguments is true.	`OR({Field 1}, {Field 2})` => 1 (if one field value is truthy)
`SWITCH(expression, [pattern, result ... , default])`	Takes an expression, a list of possible values for that expression, and for each one, a value that the expression should take in that case. It can also take a default value if the expression input doesn't match any of the defined patterns. In many cases, SWITCH() can be used instead of a nested IF formula.	`Example 1: SWITCH({Status}, "To Do", "Get this started!", "In Progress", {Due Date}, "Done", "Work complete!" )` => if the {Status} field is the value "To Do" then this would return "Get this started!" `SWITCH( 1, 1, "one", 2, "two", "many")` `=> one`
`TRUE()`	Logical value true. The value of true is represented numerically by a 1.	`IF(2 = 2, TRUE(), FALSE())` => 1
`XOR(expression1, [exp2, ...])`	Returns true if an odd number of arguments are true.	`XOR(TRUE(), FALSE(), TRUE())` => 0 `XOR(TRUE(), FALSE(), FALSE())` => 1

Numeric operators and functions

To see the below numeric functions in action, and test them out yourself, check out the "#️⃣ Numeric function examples" table in the Formula Playground.

Numeric operators

Operator	Description	Examples
`+`	Add together two numeric values	`Size + 2`
`-`	Subtract two numeric values	`Price - 3.00`
`*`	Multiply two numeric values	`Price * Quantity`
`/`	Divide two numeric values	`Price / {Num People}`

Numeric functions

Function	Description	Examples
`ABS(value)`	Returns the absolute value.	`ABS(-5)` => 5
`AVERAGE(number1, [number2, ...])`	Returns the average of the numbers.	`AVERAGE(2.3, 5.7, 6.8)` => 3.93
`CEILING(value, [significance])`	Returns the nearest integer multiple of significance that is greater than or equal to the value. If no significance is provided, a significance of 1 is assumed.	`CEILING(1.01)` => 2 `CEILING(1.01, 0.1)` => 1.1
`COUNT(number1, [number2, ....])`	Count the number of numeric items.	`COUNT(1,2,3,"","four")` => 3
`COUNTA(textOrNumber1, [number2, ....])`	Count the number of non-empty values. This function counts both numeric and text values.	`COUNTA(1,2,3,"","four")` => 4
`COUNTALL(textOrNumber1, [number2, ....])`	Count the number of all elements including text and blanks.	`COUNTALL(1,2,3,"","four")` => 5
`EVEN(value)`	Returns the smallest even integer that is greater than or equal to the specified value.	`EVEN(2.2)` => 4 `EVEN(-1.6)` => -2
`EXP(power)`	Computes Euler's number (e) to the specified power.	`EXP(1)` => 2.71828 `EXP(3)` => 20.08554
`FLOOR(value, [significance])`	Returns the nearest integer multiple of significance that is less than or equal to the value. If no significance is provided, a significance of 1 is assumed.	`FLOOR(1.99)` => 1 `FLOOR(1.99, 0.1)` => 1.9
`INT(value)`	Returns the greatest integer that is less than or equal to the specified value.	`INT(1.99)` => 1 `INT(-1.99)` => -2
`LOG(number, [base])`	Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.	`LOG(1024, 2)` => 10 `LOG(1000)` => 3
`MAX(number1, [number2, ...])`	Returns the largest of the given numbers.	`MAX({Field1}, {Field2})` `MAX(10, 100)` => 100
`MIN(number1, [number2, ...])`	Returns the smallest of the given numbers.	`MIN({Field1}, Field2})` `MIN(10, 100)` => 10
`MOD(value1, divisor)`	Returns the remainder after dividing the first argument by the second.	`MOD({Meters}, 1000)` `MOD(7, 5)` => 2
`ODD(value)`	Rounds positive value up the the nearest odd number and negative value down to the nearest odd number.	`ODD(1.1)` => 3 `ODD(-1.1)` => -3
`POWER(base, power)`	Computes the specified base to the specified power.	`POWER(3, 3)` => 27 `POWER(7, 0)` => 1
`ROUND(value, precision)`	Rounds the value to the number of decimal places given by "precision." (Specifically, ROUND will round to the nearest integer at the specified precision, with ties broken by rounding half up toward positive infinity.)	`ROUND(3.5, 0)` => 4 `ROUND(3.4, 0)` => 3
`ROUNDDOWN(value, precision)`	Rounds the value to the number of decimal places given by "precision," always rounding down, i.e., toward zero. (You must give a value for the precision or the function will not work.)	`ROUNDDOWN(1.9, 0)` => 1 `ROUNDDOWN(-1.9, 0)` => -1
`ROUNDUP(value, precision)`	Rounds the value to the number of decimal places given by "precision," always rounding up, i.e., away from zero. (You must give a value for the precision or the function will not work.)	`ROUNDUP(1.1, 0)` => 2 `ROUNDUP(-1.1, 0)` => -2
`SQRT(value)`	Returns the square root of a nonnegative number.	`SQRT(100)` => 10
`SUM(number1, [number2, ...])`	Sum together the numbers. Equivalent to number1 + number2 + ...	`SUM({Field1}, Field2})` `SUM(1,2,3)` => 6
`VALUE(text)`	Converts the text string to a number. Some exceptions apply—if the string contains certain mathematical operators(-,%) the result may not return as expected. In these scenarios we recommend using a combination of VALUE and REGEX_REPLACE to remove non-digit values from the string: `VALUE(REGEX_REPLACE(YOURSTRING, "\\D", ""))`	`VALUE("$1000")` => 1000

Date and time functions

Looking for some examples of how you can use formulas with due dates and deadlines? Check out this blog post on our top 10 time-saving date formulas.

To see the below date and time functions in action, and test them out yourself, check out the "📆 Date and time function examples" table in the Formula Playground.

Function	Description	Examples
`CREATED_TIME()`	Returns the date and time a given record was created.	`CREATED_TIME()` => 2015-11-11T22:18:17
`DATEADD([date], [#], 'units')`	Adds specified "count" units to a datetime. See the list of shared unit specifiers here. For this function we recommend using the full unit specifier, (i.e. use "years" instead of "y"), for your desired unit.	`DATEADD("07/10/19", 10, "days")` => 2019-07-20 `DATEADD("07/10/19 01:01:00", 60, "seconds")` => 2019-07-10 1:02am `DATEADD(TODAY(), 20, "days")`
`DATESTR([date])`	Formats a datetime into a string (YYYY-MM-DD).	`DATESTR("12/13/21")` => 2021-12-13
`DATETIME_DIFF([date1], [date2], 'units')`	Returns the difference between datetimes in specified units. The difference between datetimes is determined by subtracting [date2] from [date1]. This means that if [date2] is later than [date1], the resulting value will be negative. Default units are seconds. (See list of unit specifiers here.) NOTE 1: `The DATETIME_DIFF()` formula will return whole integers for any unit specifier. NOTE 2: When attempting to use `DATETIME_DIFF()` with static dates or dates that are formatted as strings you will want to nest the `DATETIME_PARSE()` function within your formula in order to turn the string into a readable date on Airtable's backend.	`DATETIME_DIFF("04/06/2019 12:00", "04/05/2019 11:00", "hours")` => 25 `DATETIME_DIFF("04/06/2019 12:00", "04/05/2019 11:00", "days")` => 1 `DATETIME_DIFF({Date Bought}, {Date Sold}, 'minutes')` => 30
`DATETIME_FORMAT([date], '[specified output format]')`	Formats a datetime into a specified string. For an explanation of how to use this function with date fields, click here. View a list of supported format specifiers.	`DATETIME_FORMAT("07/10/19", "YYYY")` => 2019 `DATETIME_FORMAT("07/10/19", "DD YYYY")` => 10 2019
`DATETIME_PARSE(date, ['input format'], ['locale'])`	Interprets a text string as a structured date, with optional input format and locale parameters. The output format will always be formatted 'M/D/YYYY h:mm a'.	`DATETIME_PARSE("4 Mar 2017 23:00", 'D MMM YYYY HH:mm')` => 3/4/2017 11:00pm
`DAY([date])`	Returns the day of the month of a datetime in the form of a number between 1-31.	`DAY("02/17/2013")` => 17
`HOUR([datetime])`	Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm).	`HOUR("4 Mar 2017 7:00")` => 7
`IS_AFTER([date1], [date2])`	Determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no.	`IS_AFTER("1/1/1979", "1/1/2000")` => 0 `IS_AFTER({Deadline}, TODAY())`
`IS_BEFORE([date1], [date2])`	Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no.	`IS_BEFORE("1/1/1979", "1/1/2000")` => 1 `IS_BEFORE({Deadline}, TODAY())`
`IS_SAME([date1], [date2], [unit])`	Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no.	`IS_SAME("1/1/1979", "1/1/1979")` => 1 `IS_SAME({Date 1}, {Date 2}, 'hour')` => 0
`LAST_MODIFIED_TIME([{field1},{field2}, ...])`	Returns the date and time of the most recent modification made by a user in a non-computed field in the table. If you only care about changes made to specific fields, you can include one or more field names, and the function will just return the date and time of the most recent change made to any of the specified fields.	`LAST_MODIFIED_TIME()` => 5/9/2019 1:27 a.m. `LAST_MODIFIED_TIME({Due Date})` => 3/16/2019 6:45 p.m.
`MINUTE([datetime])`	Returns the minute of a datetime as an integer between 0 and 59.	`MINUTE("02/17/2013 7:31")` => 31
`MONTH([date])`	Returns the month of a datetime as a number between 1 (January) and 12 (December).	`MONTH("02/17/2013 7:31")` => 2
`NOW()`	While similar to the TODAY() function, NOW() returns the current date AND time. This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.	The following examples demonstrate the return if you were to enter NOW() and TODAY() at the same time, 08/06/2020 8:03am formatted as GMT on a 12 hour clock: You can toggle time formatting (GMT/Local, 12/24 hour) on the formatting tab `NOW()` => 08/06/2020 8:03am `TODAY()` => 08/06/2020 12:03am
`SECOND([datetime])`	Returns the second of a datetime as an integer between 0 and 59.	`SECOND("02/17/2013 7:31:25")` => 25
`SET_LOCALE([date], [locale_modifier])`	Sets a specific locale for a datetime. Must be used in conjunction with DATETIME_FORMAT. A list of supported locale modifiers can be found here.	`DATETIME_FORMAT(SET_LOCALE("07/10/19 ", 'es'), 'LLLL')` => miércoles, 10 de julio de 2019 0:00
`SET_TIMEZONE([date], [tz_identifier])`	Sets a specific timezone for a datetime. Must be used in conjunction with DATETIME_FORMAT. A list of supported timezone identifiers can be found here.	`DATETIME_FORMAT(SET_TIMEZONE("07/10/19 13:00", 'Australia/Sydney'), 'M/D/YYYY h:mm')` => 7/10/2019 11:00
`TIMESTR([date/timestamp])`	Formats a datetime into a time-only string (HH:mm:ss).	`TIMESTR("02/17/2013 7:31:25")` => 7:31:25
`TONOW([date]) & FROMNOW([date])`	Calculates the number of days between the current date and another date.	`TONOW({Date})` => 25 days `FROMNOW({Date})` => 25 days
`TODAY()`	While similar to the NOW() function: TODAY() returns the current date (not the current time, if formatted, time will return 12:00am). This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.	The following examples demonstrate the return if you were to enter NOW() and TODAY() at the same time, 08/06/2020 8:03am formatted as GMT on a 12 hour clock: You can toggle time formatting (GMT/Local, 12/24 hour) on the formatting tab `NOW()` => 08/06/2020 8:03am `TODAY()` => 08/06/2020 12:03am
`WEEKDAY(date, [startDayOfWeek])`	Returns the day of the week as an integer between 0 and 6, inclusive. You may optionally provide a second argument (either "Sunday" or "Monday") to start weeks on that day. If omitted, weeks start on Sunday by default. Example: WEEKDAY(TODAY(), "Monday")	`WEEKDAY("2021-06-09")` => 3 (for Wednesday)
`WEEKNUM(date, [startDayOfWeek])`	Returns the week number in a year. You may optionally provide a second argument (either "Sunday" or "Monday") to start weeks on that day. If omitted, weeks start on Sunday by default. Example: `WEEKNUM(TODAY(), "Monday")`	`WEEKNUM("02/17/2013")` `=> 8`
`WORKDAY(startDate, numDays, [holidays])`	Returns a date that is numDays working days after startDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates.	`WORKDAY('10/16/20', 10, '2020-10-16, 2020-10-19')` => 2020-11-02 `WORKDAY({Launch date}, 100)`
`WORKDAY_DIFF(startDate, endDate, [holidays])`	Counts the number of working days between startDate and endDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates.	`WORKDAY_DIFF('10/16/20','11/02/2020', '2020-10-16, 2020-10-19')` => 10 `WORKDAY_DIFF({Assignment date}, {Due Date}, {Holidays})`
`YEAR([date])`	Returns the four-digit year of a datetime.	`YEAR("2021-06-09")` => 2021

Array functions

Note that array functions can only be used in rollup fields or when the input field is a lookup.

To see the below array functions in action, and test them out yourself, check out the "[] Array function examples" table in the Formula Playground.

Function	Description	Examples
`ARRAYCOMPACT(values)`	Removes empty strings and null values from the array. Keeps "false" and strings that contain one or more blank characters.	values = [1,2,3,"",null,false," "] `ARRAYCOMPACT(values)` => [1,2,3,false," "]
`ARRAYFLATTEN(values)`	Flattens the array by removing any array nesting. All items become elements of a single array.	values = [[1,2,"",3],[false]] `ARRAYFLATTEN(values)` => [1,2,3,false]
`ARRAYJOIN(values, separator)`	Join the array of rollup items into a string with a separator.	values = [1,2,3] `ARRAYJOIN(values, "; ")` => "1; 2; 3"
`ARRAYUNIQUE(values)`	Returns only unique items in the array.	values = [1,2,3,3,2,1] `ARRAYUNIQUE(values)` => [1,2,3]

Record functions

To see the below record functions in action, and test them out yourself, check out the "✍️ Record function examples" table in the Formula Playground.

Function	Description	Examples
`CREATED_TIME()`	Returns the creation time of the current record.	`"Created on " & CREATED_TIME()`
`LAST_MODIFIED_TIME()`	Returns the date and time of the most recent modification made by a user in a non-computed field in the table. If you only care about changes made to specific fields, you can include one or more field names, and the function will just return the date and time of the most recent change made to any of the specified fields.	`"Last modified on " & LAST_MODIFIED_TIME()`
`RECORD_ID()`	Returns the ID of the current record.	`"https://awesomeservice.com/view?recordId=" & RECORD_ID()`

REGEX functions

Regular expressions (or REGEX) can be used to match character combinations within text strings. Airtable's REGEX functions are implemented using the RE2 regular expression library. You'll find more information specific to REGEX functions in this support article.

To see the below REGEX functions in action, and test them out yourself, check out the "🔎 REGEX function examples" table in the Formula Playground.

Function	Description	Examples
`REGEX_MATCH(string, regex)`	Returns whether the input text matches a regular expression.	`REGEX_MATCH("Hello World", "Hello.World") => 1`
`REGEX_EXTRACT(string, regex)`	Returns the first substring that matches a regular expression.	`REGEX_EXTRACT("Hello World", "W.*") => "World"`
`REGEX_REPLACE(string, regex, replacement)`	Substitutes all matching substrings with a replacement string value.	`REGEX_REPLACE("Hello World", " W.*", "") => "Hello"`

Was this article helpful?

What's Next

Formulas vs other computed fields

Table of contents

 Introduction
Expressions
Text operators and functions
Logical operators and functions
Numeric operators and functions
Date and time functions
Array functions
Record functions
REGEX functions