Concatenate text values into a single text value.
&
{Last name} & ", " & {First name} => Pig, Peppa
Joins together the text arguments into a single text value.
CONCATENATE(text1, [text2, ...])
CONCATENATE("Bob"," - ", 43)
Removes whitespace at the beginning and end of string.
TRIM(string)
TRIM(" Hello! ") => "Hello"
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
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"
Replaces occurrences of old_text with new_text.
SUBSTITUTE(string, old_text, new_text, [index])
SUBSTITUTE("gold mold", "old", "et") => "get met"
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
Makes a string lowercase.
LOWER(string)
LOWER("Hello!") => "hello!"
Makes string uppercase.
UPPER(string)
UPPER("Hello!") => "HELLO!"
Repeats string by the specified number of times.
REPT(string, number)
REPT("Hi! ", 3) => "Hi! Hi! Hi!"
Extract a substring of count characters starting at whereToStart.
MID(string, whereToStart, count)
MID("quick brown fox", 6, 5) => "brown"
Returns the argument if it is text and blank otherwise.
T(value1)
T("hello") => "hello"
Extract howMany characters from the end of the string.
RIGHT(string, howMany)
RIGHT("quick brown fox", 5) => "n fox"
Extract how many characters from the beginning of the string.
LEFT(string, howMany)
LEFT("quick brown fox", 5) => "quick"
Returns the length of a string.
LEN(string)
LEN("quick brown fox") => 15
Create a line break when inserted between other text in a formula.
'\n'
CONCATENATE({Students} & '\n' & {Notes})
Smart quotes (ββ)
appear curved and will cause an error in Airtable. Instead, use only straight-lined quotes within formulas.
""
"Hello friend!"
Extract a substring of count characters starting at whereToStart.
MID(string, whereToStart, count)
MID("quick brown fox", 6, 5) => "brown"
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"
Compare if one value is greater than another value.
>
3 > 2
Compare if one value is equal to another value.
=
5 = 5
Check if one value is not equal to another value.
!=
3 != 2
Compare if one value is greater than, or equal to, another value.
>=
3 >= 3
Compare if one value is less than another value.
<
3 < 2
Compare if one value is less than, or equal to, another value.
<=
2 <= 2
Returns true if all the arguments are true, returns false otherwise.
AND(expression, [exp2, ...])
AND({Field 1}, {Field 2})
Returns a generic Error value (#ERROR!).
ERROR()
IF({Price} < 0, ERROR(), "More than zero!")
Returns a blank value.
BLANK()
IF({Price},{Price} + 10, BLANK())
Logical value false. False is represented numerically by a 0.
FALSE() => 0
IF(insertCondition, TRUE(), FALSE())
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")
Reverses the logical value of its argument.
NOT(expression)
NOT({Total} = 0)
Returns true if any one of the arguments is true.
OR(expression, [exp2, ...])
OR({Field 1}, {Field 2})
Returns true if the expression causes an error.
ISERROR(expression)
ISERROR(2/0)
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},
Returns true if an odd number of arguments are true.
XOR(expression, [exp2, ...])
XOR(TRUE(), FALSE(), TRUE()) => 0
Logical value true. The value of true is represented numerically by a 1.
TRUE() => 1
IF(insertCondition, TRUE(), FALSE())
Returns the absolute value.
ABS(value)
ABS(-5) => 5
Add together two values
+
5 + 5 => 10
Subtract two values
-
5 - 3 => 2
Multiply two values
*
5 * 5 => 25
Divide two values
/
50 / 10 => 5
Count the number of non-empty values. This function counts both numeric and text values.
COUNTA(textOrNumber1, [number2, ....])
COUNTA(1,2,3,"","four") => 4
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
Count the number of numeric items.
COUNT(number1, [number2, ....])
COUNT(1,2,3,"","four") => 3
Returns the average of the numbers.
AVERAGE(number1, [number2, ...])
AVERAGE(1,2,3,4,5) => 3
Returns the smallest even integer that is greater than or equal to the specified value.
EVEN(value)
EVEN(2.2) => 4
Count the number of all elements including text and blanks.
COUNTALL(textOrNumber1, [number2, ....])
COUNTALL(1,2,3,"","four") => 5
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
Computes Euler's number (e) to the specified power.
EXP(power)
EXP(1) => 2.71828
Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.
LOG(number, [base])
LOG(1024, 2) => 10
Returns the greatest integer that is less than or equal to the specified value.
INT(value)
INT(1.99)=> 1
Returns the remainder after dividing the first argument by the second.
MOD(value, divisor)
MOD(7, 5) => 2
Computes the specified base to the specified power.
POWER(base, power)
POWER(3, 3) => 27
Rounds positive value up the the nearest odd number and negative value down to the nearest odd number.
ODD(value)
ODD(1.1) => 3
Returns the largest of the given numbers.
MAX(number1, [number2, ...])
MAX({Field1}, {Field2})
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
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
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
Sum together the numbers. Equivalent to number1 + number2 + ...
SUM(number1, [number2, ...])
SUM({Field1}, Field2})
Returns the square root of a nonnegative number.
SQRT(value)
SQRT(100) => 10
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
Returns the smallest of the given numbers.
MIN(number1, [number2, ...])
MIN({Field1}, Field2})
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
Formats a datetime into a string (YYYY-MM-DD).
DATESTR([date])
DATESTR("12/13/21") => 2021-12-13
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
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
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
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
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
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
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
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
Returns the month of a datetime as a number between 1 (January) and 12 (December).
MONTH([date])
MONTH("02/17/2013 7:31") => 2
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
Returns the minute of a datetime as an integer between 0 and 59.
MINUTE([datetime])
MINUTE("02/17/2013 7:31") => 31
Returns the second of a datetime as an integer between 0 and 59.
SECOND([datetime])
SECOND("02/17/2013 7:31:25") => 25
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
While similar to the TODAY() function, NOW() returns the current date AND time.
NOW()
NOW() => 2021-06-09 4:19pm
Calculates the number of days between the current date and another date.
TONOW([date])
TONOW("2021-07-01")
Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday).
WEEKDAY([date])
WEEKDAY("2021-06-09")
Formats a datetime into a time-only string (HH:mm:ss).
TIMESTR([date/timestamp])
TIMESTR("02/17/2013 7:31:25") => 7:31:25
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
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
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')
Returns the four-digit year of a datetime.
YEAR([date])
YEAR("2021-06-09") => 2021
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
Calculates the number of days between the current date and another date.
FROMNOW([date])
FROMNOW("2021-07-01")
Join the array of items into a string with a separator.
ARRAYJOIN([item1, item2, item3], separator)
ARRAYJOIN(values, ";")
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)
Flattens the array by removing any array nesting. All items become elements of a single array.
ARRAYFLATTEN([item1, item2, item3])
ARRAYFLATTEN(values)
Returns only unique items in the array.
ARRAYUNIQUE([item1, item2, item3])
ARRAYUNIQUE(values)
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()
Returns the date and time a given record was created.
CREATED_TIME()
CREATED_TIME()
Returns the ID of the current record.
RECORD_ID()
RECORD_ID() => rec**************
Returns whether the input text matches a regular expression.
REGEX_MATCH(string, regex)
REGEX_MATCH("Hello World", "Hello.World") => 1
Returns the first substring that matches a regular expression.
REGEX_EXTRACT(string, regex)
REGEX_EXTRACT("Hello World", "W.*")
Substitutes all matching substrings with a replacement string value.
REGEX_REPLACE(string, regex, replacement)
REGEX_REPLACE("Hello World", " W.*", "") => "Hello"
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}