Formula Playground
Copy base
Report abuse
Views
Name
1
&
2
CONCATENATE()
3
TRIM()
4
SEARCH()
5
ENCODE_URL_COMPONENT()
6
SUBSTITUTE()
7
FIND()
8
LOWER()
9
UPPER()
10
REPT()
11
MID()
12
T()
13
RIGHT()
14
LEFT()
15
LEN()
16
' '
17
β€œβ€
18
MID()
19
REPLACE()
20
>
21
=
22
!=
23
>=
24
<
25
<=
26
AND()
27
ERROR()
28
BLANK()
29
FALSE()
30
IF()
31
NOT()
32
OR()
33
ISERROR()
34
SWITCH()
35
XOR()
36
TRUE()
37
ABS()
38
+
39
-
40
*
41
/
42
COUNTA()
43
CEILING()
44
COUNT()
45
AVERAGE()
46
EVEN()
47
COUNTALL()
48
FLOOR()
49
EXP()
50
LOG()
51
INT()
52
MOD()
53
POWER()
54
ODD()
55
MAX()
56
ROUND()
57
ROUNDUP()
58
ROUNDDOWN()
59
SUM()
60
SQRT()
61
VALUE()
62
MIN()
63
DATEADD()
64
DATESTR()
65
DATETIME_DIFF()
66
DATETIME_PARSE()
67
DATETIME_FORMAT()
68
DAY()
69
HOUR()
70
IS_BEFORE()
71
IS_AFTER()
72
IS_SAME()
73
MONTH()
74
SET_LOCALE()
75
MINUTE()
76
SECOND()
77
SET_TIMEZONE()
78
NOW()
79
TONOW()
80
WEEKDAY()
81
TIMESTR()
82
WORKDAY()
83
WEEKNUM()
84
WORKDAY_DIFF()
85
YEAR()
86
TODAY()
87
FROMNOW()
88
ARRAYJOIN()
89
ARRAYCOMPACT()
90
ARRAYFLATTEN()
91
ARRAYUNIQUE()
92
LAST_MODIFIED_TIME()
93
CREATED_TIME()
94
RECORD_ID()
95
REGEX_MATCH()
96
REGEX_EXTRACT()
97
REGEX_REPLACE()
98
{}
Drag to adjust the number of frozen columns
Formula Data Type
Description
Technical Function Usage
Example Function Usage
✏️ Text operators and functions

Concatenate text values into a single text value.

&
{Last name} & ", " & {First name} => Pig, Peppa
✏️ Text operators and functions

Joins together the text arguments into a single text value.

CONCATENATE(text1, [text2, ...])
CONCATENATE("Bob"," - ", 43)
✏️ Text operators and functions

Removes whitespace at the beginning and end of string.

TRIM(string)
TRIM(" Hello! ") => "Hello"
✏️ Text operators and functions

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.

SEARCH(stringToFind, whereToSearch,[startFromPosition])
SEARCH("World", "Hello World") => 7
✏️ Text operators and functions

Replaces certain characters with encoded equivalents for use in constructing URLs or URIs. Does not encode the following characters: - _ . ~

ENCODE_URL_COMPONENT(component_string)
ENCODE_URL_COMPONENT("chicken & waffles") => "chicken%20%26%20waffles"
✏️ Text operators and functions

Replaces occurrences of old_text with new_text.

SUBSTITUTE(string, old_text, new_text, [index])
SUBSTITUTE("gold mold", "old", "et") => "get met"
✏️ Text operators and functions

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.

FIND(stringToFind, whereToSearch,[startFromPosition])
FIND("fox", "quick brown fox") => 13
✏️ Text operators and functions

Makes a string lowercase.

LOWER(string)
LOWER("Hello!") => "hello!"
✏️ Text operators and functions

Makes string uppercase.

UPPER(string)
UPPER("Hello!") => "HELLO!"
✏️ Text operators and functions

Repeats string by the specified number of times.

REPT(string, number)
REPT("Hi! ", 3) => "Hi! Hi! Hi!"
✏️ Text operators and functions

Extract a substring of count characters starting at whereToStart.

MID(string, whereToStart, count)
MID("quick brown fox", 6, 5) => "brown"
✏️ Text operators and functions

Returns the argument if it is text and blank otherwise.

T(value1)
T("hello") => "hello"
✏️ Text operators and functions

Extract howMany characters from the end of the string.

RIGHT(string, howMany)
RIGHT("quick brown fox", 5) => "n fox"
✏️ Text operators and functions

Extract how many characters from the beginning of the string.

LEFT(string, howMany)
LEFT("quick brown fox", 5) => "quick"
✏️ Text operators and functions

Returns the length of a string.

LEN(string)
LEN("quick brown fox") => 15
✏️ Text operators and functions

Create a line break when inserted between other text in a formula.

'\n'
CONCATENATE({Students} & '\n' & {Notes})
✏️ Text operators and functions

Smart quotes (β€œβ€) appear curved and will cause an error in Airtable. Instead, use only straight-lined quotes within formulas.

""
"Hello friend!"
✏️ Text operators and functions

Extract a substring of count characters starting at whereToStart.

MID(string, whereToStart, count)
MID("quick brown fox", 6, 5) => "brown"
✏️ Text operators and functions

Replaces the number of characters beginning with the start character with the replacement text.

REPLACE(string, start_character, number_of_characters, replacement)
REPLACE("database", 2, 5, "o") => "dose"
❓ Logical operators and functions

Compare if one value is greater than another value.

>
3 > 2
❓ Logical operators and functions

Compare if one value is equal to another value.

=
5 = 5
❓ Logical operators and functions

Check if one value is not equal to another value.

!=
3 != 2
❓ Logical operators and functions

Compare if one value is greater than, or equal to, another value.

>=
3 >= 3
❓ Logical operators and functions

Compare if one value is less than another value.

<
3 < 2 
❓ Logical operators and functions

Compare if one value is less than, or equal to, another value.

<=
2 <= 2
❓ Logical operators and functions

Returns true if all the arguments are true, returns false otherwise.

AND(expression, [exp2, ...])
AND({Field 1}, {Field 2})
❓ Logical operators and functions

Returns a generic Error value (#ERROR!).

ERROR()
IF({Price} < 0, ERROR(), "More than zero!")
❓ Logical operators and functions

Returns a blank value.

BLANK()
IF({Price},{Price} + 10,  BLANK())
❓ Logical operators and functions

Logical value false. False is represented numerically by a 0.

FALSE() => 0
IF(insertCondition, TRUE(), FALSE())
❓ Logical operators and functions

Returns value1 if the logical argument is true, otherwise it returns value2. Can also be used to make nested IF statements.

IF(expression, ifTrue, ifFalse)
 IF({Due Date} = BLANK(), "Please enter date", "Date entered")
❓ Logical operators and functions

Reverses the logical value of its argument.

NOT(expression)
NOT({Total} = 0)
❓ Logical operators and functions

Returns true if any one of the arguments is true.

OR(expression, [exp2, ...])
OR({Field 1}, {Field 2})
❓ Logical operators and functions

Returns true if the expression causes an error.

ISERROR(expression)
ISERROR(2/0)
❓ Logical operators and functions

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.

SWITCH(expression, [pattern, result ... , default])
SWITCH({Status},
❓ Logical operators and functions

Returns true if an odd number of arguments are true.

XOR(expression, [exp2, ...])
XOR(TRUE(), FALSE(), TRUE()) => 0
❓ Logical operators and functions

Logical value true. The value of true is represented numerically by a 1.

TRUE() => 1
IF(insertCondition, TRUE(), FALSE())
#️⃣ Numeric operators and functions

Returns the absolute value.

ABS(value)
ABS(-5) => 5
#️⃣ Numeric operators and functions

Add together two values

+
5 + 5 => 10
#️⃣ Numeric operators and functions

Subtract two values

-
5 - 3 => 2
#️⃣ Numeric operators and functions

Multiply two values

*
5 * 5 => 25
#️⃣ Numeric operators and functions

Divide two values

/
50 / 10 => 5
#️⃣ Numeric operators and functions

Count the number of non-empty values. This function counts both numeric and text values.

COUNTA(textOrNumber1, [number2, ....])
COUNTA(1,2,3,"","four") => 4
#️⃣ Numeric operators and functions

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(value, [significance])
CEILING(1.01) => 2
#️⃣ Numeric operators and functions

Count the number of numeric items.

COUNT(number1, [number2, ....])
COUNT(1,2,3,"","four") => 3
#️⃣ Numeric operators and functions

Returns the average of the numbers.

AVERAGE(number1, [number2, ...]) 
AVERAGE(1,2,3,4,5) => 3
#️⃣ Numeric operators and functions

Returns the smallest even integer that is greater than or equal to the specified value.

EVEN(value)
EVEN(2.2) => 4
#️⃣ Numeric operators and functions

Count the number of all elements including text and blanks.

COUNTALL(textOrNumber1, [number2, ....])
COUNTALL(1,2,3,"","four") => 5
#️⃣ Numeric operators and functions

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(value, [significance])
FLOOR(1.99) => 1
#️⃣ Numeric operators and functions

Computes Euler's number (e) to the specified power.

EXP(power)
EXP(1) => 2.71828
#️⃣ Numeric operators and functions

Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.

LOG(number, [base])
LOG(1024, 2) => 10
#️⃣ Numeric operators and functions

Returns the greatest integer that is less than or equal to the specified value.

INT(value)
INT(1.99)=> 1
#️⃣ Numeric operators and functions

Returns the remainder after dividing the first argument by the second.

MOD(value, divisor)
MOD(7, 5) => 2
#️⃣ Numeric operators and functions

Computes the specified base to the specified power.

POWER(base, power)
POWER(3, 3) => 27
#️⃣ Numeric operators and functions

Rounds positive value up the the nearest odd number and negative value down to the nearest odd number.

ODD(value)
ODD(1.1) => 3
#️⃣ Numeric operators and functions

Returns the largest of the given numbers.

MAX(number1, [number2, ...])
MAX({Field1}, {Field2})
#️⃣ Numeric operators and functions

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(value, precision)
ROUND(3.5, 0) => 4
#️⃣ Numeric operators and functions

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(value, precision)
ROUNDUP(3.5, 0) => 4
#️⃣ Numeric operators and functions

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(value, precision)
ROUNDDOWN(3.5, 0) => 3
#️⃣ Numeric operators and functions

Sum together the numbers. Equivalent to number1 + number2 + ...

SUM(number1, [number2, ...])
SUM({Field1}, Field2})
#️⃣ Numeric operators and functions

Returns the square root of a nonnegative number.

SQRT(value)
SQRT(100) => 10
#️⃣ Numeric operators and functions

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(text)
VALUE("$1000") => 1000
#️⃣ Numeric operators and functions

Returns the smallest of the given numbers.

MIN(number1, [number2, ...])
MIN({Field1}, Field2})
πŸ“† Date and time functions

Adds specified "count" units to a datetime. (See list of shared unit specifiers here. For this function we recommend using the full unit specifier for your desired unit.) 

DATEADD([date], [#], 'units')
DATEADD("07/10/19", 10, "days") => 2019-07-20
πŸ“† Date and time functions

Formats a datetime into a string (YYYY-MM-DD). 

DATESTR([date])
DATESTR("12/13/21") => 2021-12-13
πŸ“† Date and time functions

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. 

DATETIME_DIFF([date1], [date2], 'units')
DATETIME_DIFF("04/06/2019 12:00", "04/05/2019 11:00", "hours") => 25
πŸ“† Date and time functions

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(date, ['input format'], ['locale'])
DATETIME_PARSE("4 Mar 2017 23:00", 'D MMM YYYY HH:mm') => 3/4/2017 11:00pm 
πŸ“† Date and time functions

Formats a datetime into a specified string. For an explanation of how to use this function with date fields, click here. For a list of supported format specifiers, please click here.

DATETIME_FORMAT([date], '[specified output format]')
DATETIME_FORMAT("07/10/19", "YYYY") => 2019
πŸ“† Date and time functions

Returns the day of the month of a datetime in the form of a number between 1-31.

DAY([date])
DAY("02/17/2013") => 17
πŸ“† Date and time functions

Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm).

HOUR([datetime])
HOUR("4 Mar 2017 7:00") => 7
πŸ“† Date and time functions

Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no.

IS_BEFORE([date1], [date2])
IS_BEFORE("1/1/1979", "1/1/2000") => 1
πŸ“† Date and time functions

Determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no.

IS_AFTER([date1], [date2])
IS_AFTER("1/1/1979", "1/1/2000") => 0
πŸ“† Date and time functions

Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no.

IS_SAME([date1], [date2], [unit])
IS_SAME({Date 1}, {Date 2}, 'hour') => 0
πŸ“† Date and time functions

Returns the month of a datetime as a number between 1 (January) and 12 (December).

MONTH([date])
MONTH("02/17/2013 7:31") => 2
πŸ“† Date and time functions

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.

SET_LOCALE([date], [locale_modifier])
DATETIME_FORMAT(SET_LOCALE("07/10/19
πŸ“† Date and time functions

Returns the minute of a datetime as an integer between 0 and 59.

MINUTE([datetime])
MINUTE("02/17/2013 7:31") => 31
πŸ“† Date and time functions

Returns the second of a datetime as an integer between 0 and 59.

SECOND([datetime])
SECOND("02/17/2013 7:31:25") => 25
πŸ“† Date and time functions

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.

SET_TIMEZONE([date], [tz_identifier])
DATETIME_FORMAT(SET_TIMEZONE("07/10/19 13:00", 'Australia/Sydney'), 'M/D/YYYY h:mm') => 7/10/2019 11:00
πŸ“† Date and time functions

While similar to the TODAY() function, NOW() returns the current date AND time.

NOW()
NOW() => 2021-06-09 4:19pm
πŸ“† Date and time functions

Calculates the number of days between the current date and another date.

TONOW([date])
TONOW("2021-07-01") 
πŸ“† Date and time functions

Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday).

WEEKDAY([date])
WEEKDAY("2021-06-09")
πŸ“† Date and time functions

Formats a datetime into a time-only string (HH:mm:ss).  

TIMESTR([date/timestamp])
TIMESTR("02/17/2013 7:31:25") => 7:31:25
πŸ“† Date and time functions

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(startDate, numDays, [holidays])
WORKDAY('10/16/20', 10, '2020-10-16, 2020-10-19') => 2020-11-02
πŸ“† Date and time functions

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([date])
WEEKNUM("02/17/2013") => 8
πŸ“† Date and time functions

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(startDate, endDate, [holidays])
WORKDAY_DIFF('10/16/20','11/02/2020', '2020-10-16, 2020-10-19')
πŸ“† Date and time functions

Returns the four-digit year of a datetime.

YEAR([date])
YEAR("2021-06-09") => 2021
πŸ“† Date and time functions

While similar to the NOW() function: TODAY() returns the current date (not the current time, if formatted, time will return 12:00am).

TODAY()
TODAY() => 2021-06-09 12:00am
πŸ“† Date and time functions

Calculates the number of days between the current date and another date.

FROMNOW([date])
FROMNOW("2021-07-01")
[] Array functions

Join the array of items into a string with a separator.

ARRAYJOIN([item1, item2, item3], separator)
ARRAYJOIN(values, ";")
[] Array functions

Removes empty strings and null values from the array. Keeps "false" and strings that contain one or more blank characters.

ARRAYCOMPACT([item1, item2, item3])
ARRAYCOMPACT(values)
[] Array functions

Flattens the array by removing any array nesting. All items become elements of a single array.

ARRAYFLATTEN([item1, item2, item3])
ARRAYFLATTEN(values)
[] Array functions

Returns only unique items in the array.

ARRAYUNIQUE([item1, item2, item3])
ARRAYUNIQUE(values)
✍️ Record functions

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([{field1},{field2}, ...])
LAST_MODIFIED_TIME()
✍️ Record functions

Returns the date and time a given record was created. 

CREATED_TIME()
CREATED_TIME()
✍️ Record functions

Returns the ID of the current record.

RECORD_ID()
RECORD_ID() => rec**************
πŸ”Ž Regex functions

Returns whether the input text matches a regular expression.

REGEX_MATCH(string, regex)
REGEX_MATCH("Hello World", "Hello.World") => 1
πŸ”Ž Regex functions

Returns the first substring that matches a regular expression.

REGEX_EXTRACT(string, regex)
REGEX_EXTRACT("Hello World", "W.*")
πŸ”Ž Regex functions

Substitutes all matching substrings with a replacement string value.

REGEX_REPLACE(string, regex, replacement)
REGEX_REPLACE("Hello World", " W.*", "") => "Hello" 
Formula formatting

When referencing a field, it's a best practice to surround the field name with curly brackets (e.g. {Due Date}). When these brackets are missing the formula field may error out with the following message: "Sorry, there was a problem creating this field. Invalid formula. Please check your formula text."

{Field Name Here}
{Due Date}
98 records
  • All Formulas
  • Formula Formatting
  • ✏️Text operators & functions
  • ❓Logical operators & functions
  • #️⃣ Numeric operators & functions
  • πŸ“† Date and time functions
  • πŸ”Ž Regex functions
  • ✍️ Record functions
  • [] Array functions
Extensions

Alert

Lorem ipsum
Okay