&

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"

ARRAYJOIN([item1, item2, item3], 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)

LEFT("quick brown fox", 5)

=> quick

LEN(string)

LEN("quick brown fox")

=> 15

LOWER(string)

LOWER("Hello!")

=> hello!

MID(string, whereToStart, count)

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)

REPT("Hi! ", 3)

=> Hi! Hi! Hi!

RIGHT(string, howMany)

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)

T("text only")

=> text only

T(42)

=> blank

TRIM(string)

TRIM(" Hello! ")

=> Hello!

UPPER(string)

UPPER("Hello!")

=> HELLO!

>

3 > 2

=> TRUE

<

2 < 3

=> TRUE

>=

3 >= 3

=> TRUE

<=

2 <= 2

=> TRUE

=

2 = 2

=> TRUE

!=

3 != 2

=> TRUE

AND(logical1, [logical2, ...])

AND({Field 1}, {Field 2})

BLANK()

IF({Price} > 1000000, "Wow, that's pretty expensive", BLANK())

ERROR()

IF({Price} < 0, ERROR(), "More than zero!")

FALSE()

IF(2 = 3, TRUE(), FALSE())

=> 0 

IF(logical, value1, value2)

Returns value1 if the logical argument 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)

ISERROR(2/0)

=> 1 (true because of  the "divide by zero" error)

NOT(expression)

NOT({Total} = 0)

OR(logical1, [logical2, ...])

OR({Field 1}, {Field 2})
=> 1 (if one field value is truthy)

SWITCH(expression, [pattern, result ... , default])

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()

IF(2 = 2, TRUE(), FALSE())

=> 1 

XOR(logical1, [logical2, ...])

=> 0

=> 1

+

Size + 2

-

*

Price * Quantity 

/

Price / {Num People}

ABS(value)

ABS(-5)

=> 5

AVERAGE(number1, [number2, ...])

AVERAGE(2.3, 5.7, 6.8)

=> 3.93

CEILING(value, [significance])

CEILING(1.01)

=> 2

CEILING(1.01, 0.1)

=> 1.1

COUNT(number1, [number2, ....])

COUNT(1,2,3,"","four")

=> 3

COUNTA(textOrNumber1, [number2, ....])

COUNTA(1,2,3,"","four")

=> 4

COUNTALL(textOrNumber1, [number2, ....])

COUNTALL(1,2,3,"","four")

=> 5

EVEN(value)

EVEN(2.2)

=> 4

EVEN(-1.6)

=> -2

EXP(power)

EXP(1)

=> 2.71828

EXP(3)

=> 20.08554

FLOOR(value, [significance])

FLOOR(1.99)

=> 1

FLOOR(1.99, 0.1)

=> 1.9

INT(value)

INT(1.99)

=> 1

INT(-1.99)

=> -2

LOG(number, [base])

LOG(1024, 2)

=> 10

LOG(1000)

=> 3

MAX(number1, [number2, ...])

=> 100

MIN(number1, [number2, ...])

=> 10

MOD(value1, divisor)

MOD({Meters}, 1000)

MOD(7, 5)

=> 2

ODD(value)

ODD(1.1)

=> 3

ODD(-1.1)

=> -3

POWER(base, power)

POWER(3, 3)

=> 27

POWER(7, 0)

=> 1

ROUND(value, precision)

ROUND(3.5, 0)

=> 4

ROUND(3.4, 0)

=> 3

ROUNDDOWN(value, precision)

ROUNDDOWN(1.9, 0)

=> 1

ROUNDDOWN(-1.9, 0)

=> -1

ROUNDUP(value, precision)

ROUNDUP(1.1, 0)

=> 2

ROUNDUP(-1.1, 0)

=> -2

SQRT(value)

SQRT(100)

=> 10

SUM(number1, [number2, ...])

=> 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

Function

CREATED_TIME()

CREATED_TIME()

=> 2015-11-11T22:18:17 

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("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. 

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("07/10/19", "YYYY")

=> 2019

DATETIME_FORMAT("07/10/19", "DD  YYYY")

=> 10 2019

DATETIME_PARSE("4 Mar 2017 23:00", 'D MMM YYYY HH:mm')

=> 3/4/2017 11:00pm 

DAY("02/17/2013")

=> 17 

HOUR("4 Mar 2017 7:00")

=> 7

IS_AFTER("1/1/1979", "1/1/2000")

=> 0

IS_AFTER({Deadline}, TODAY())

IS_BEFORE("1/1/1979", "1/1/2000")

=> 1

IS_BEFORE({Deadline}, TODAY())

IS_SAME("1/1/1979", "1/1/1979")

=> 1

IS_SAME({Date 1}, {Date 2}, 'hour')

=> 0

LAST_MODIFIED_TIME()

=> 5/9/2019 1:27 a.m.

LAST_MODIFIED_TIME({Due Date})

=> 3/16/2019 6:45 p.m.

MINUTE("02/17/2013 7:31")

=> 31  

MONTH("02/17/2013 7:31")

=> 2

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("02/17/2013 7:31:25")

=> 25

DATETIME_FORMAT(SET_LOCALE("07/10/19
", 'es'), 'LLLL')

=> miércoles, 10 de julio de 2019 0:00

DATETIME_FORMAT(SET_TIMEZONE("07/10/19 13:00", 'Australia/Sydney'), 'M/D/YYYY h:mm')

=> 7/10/2019 11:00

TIMESTR("02/17/2013 7:31:25")

=> 7:31:25

TONOW({Date})

=> 25 days 

FROMNOW({Date})

=> 25 days 

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("2021-06-09")

=> 3 (for Wednesday)

WEEKNUM("02/17/2013")

=> 8

WORKDAY('10/16/20', 10, '2020-10-16, 2020-10-19')

=> 2020-11-02

WORKDAY({Launch date}, 100)

WORKDAY_DIFF('10/16/20','11/02/2020', '2020-10-16, 2020-10-19')

=> 10

WORKDAY_DIFF({Assignment date}, {Due Date}, {Holidays})

YEAR("2021-06-09")

=> 2021

ARRAYCOMPACT(values)

values = [1,2,3,"",null,false," "]

ARRAYCOMPACT(values)

=> [1,2,3,false," "]

ARRAYFLATTEN(values)

values = [[1,2,"",3],[false]]

ARRAYFLATTEN(values)

=> [1,2,3,false]

ARRAYJOIN(values, separator)

values = [1,2,3]

ARRAYJOIN(values, "; ") 

=> "1; 2; 3"

ARRAYUNIQUE(values)

values = [1,2,3,3,2,1]

ARRAYUNIQUE(values)

=> [1,2,3]

CREATED_TIME()

"Created on " & CREATED_TIME()

LAST_MODIFIED_TIME()

"Last modified on " & LAST_MODIFIED_TIME()

RECORD_ID()

"https://awesomeservice.com/view?recordId=" & RECORD_ID()

REGEX_MATCH(string, regex)

REGEX_MATCH("Hello World", "Hello.World") => 1

REGEX_EXTRACT(string, regex)

REGEX_EXTRACT("Hello World", "W.*") => "World"

REGEX_REPLACE(string, regex, replacement)

REGEX_REPLACE("Hello World", " W.*", "") => "Hello"