Formula field reference

Operator

&

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"

Function

ARRAYJOIN([item1, item2, item3], separator)

ARRAYJOIN([1,2,3], "; ")

=> 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(Name," - ", Age)

=> Bob - 43

CONCATENATE("\"",{Product Name},"\"")

=> "GreatProduct"

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!

Operator

>

3 > 2

=> TRUE

<

2 < 3

=> TRUE

>=

3 >= 3

=> TRUE

<=

2 <= 2

=> TRUE

=

2 = 2

=> TRUE

!=

3 != 2

=> TRUE

Function

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

AND(Finished, Reviewed)

BLANK()

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

ERROR()

IF(total < 0, ERROR(), "OK")

FALSE()

FALSE()

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(Date = BLANK(), "Please enter date", "Date entered")

ISERROR(expr)

ISERROR(2/0)

NOT(boolean)

NOT(Total > 50)

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

OR(Finished, Reviewed)

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

SWITCH(4, 1, "one", 2, "two", "many")

=> many

SWITCH(1, 1, "one", 2, "two", "many")

=> one

TRUE()

IF(Total > 50, TRUE(), FALSE())

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

XOR(TRUE(), Done, Final)

Operator

+

Size + 2

-

*

Price * Quantity 

/

Price / {Num People}

Function

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

LOG(1024, 2)

=> 10

LOG(1000)

=> 3

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

MAX(Amount, 1000)

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

MIN(Amount, 0.0)

MOD(value1, divisor)

MOD(meters, 1000)

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, ...])

SUM(savings, checking, investments)

VALUE(text)

VALUE("$1000")

=> 1000

Function

CREATED_TIME()

CREATED_TIME()

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

DATEADD(Date, 10, 'days')

=> 10/9/2015 12:00am

DATESTR({Date})

=> 2015-11-12

Returns the difference between datetimes in specified units. Default units are seconds. (See list of unit specifiers here.)

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({Date}, TODAY(), 'days')

=> 15

DATETIME_FORMAT(TODAY(), 'DD-MM-YYYY')

=> 10-11-2015 

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

=> 3/4/2017 11:00pm 

DAY({Completion date})

=> 24 

HOUR({Completion date})

=> 9

IS_AFTER({Deadline}, TODAY())

=> 0

IS_BEFORE({Deadline}, TODAY())

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

=> 31  

MONTH({Completion date})

=> 10

SECOND(CREATED_TIME())

=> 53

DATETIME_FORMAT(SET_LOCALE(NOW(), 'ru'), 'lll')

=> 9 июня 2016 г., 23:49

DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Sydney'), 'M/D/YYYY h:mm')

=> 11/12/2015 7:16pm

TIMESTR(NOW())

=> 04:52:12 

TONOW({Date})

=> 25 days 

NOW()

=> 11/12/2015 12:00am

WEEKDAY({Date})

=> 4  

WEEKNUM({Date})

=> 46

WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10')

=> 6/20/2017

WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10')

=> 8

YEAR({Completion date})

=> 2015

Function

ARRAYCOMPACT([item1, item2, item3])

ARRAYCOMPACT([1,2,"",3,false," ", null])

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

ARRAYFLATTEN([item1, item2, item3])

ARRAYFLATTEN([1,2," ",3,],[false])

=> [1,2,3,false]

ARRAYJOIN([item1, item2, item3], separator)

ARRAYJOIN([1,2,3], "; ")

=> "1; 2; 3"

ARRAYUNIQUE([item1, item2, item3])

ARRAYUNIQUE([1,2,3,3,1])

=> "[1,2,3]"

Function

CREATED_TIME()

"Created on " & CREATED_TIME()

RECORD_ID()

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