Formula

We can use formulas to gather data from the other fields into an equation and create a new field with the result.

Chat icon
Transcript

In this article:

In order to create a formula, we should first choose the field type of the result; it can be a text, number, date, boolean, website link or a currency.



After choosing the field type of the end result, we can start creating our formula by clicking on the box below ('to be computed'). On the list that will open we can see the fields we have and the operands we can use. A formula can start with a field or with an operands such as MIN(), MAX(), COUNT() or MonthName...


Example - 1 :

We have a social media planning app and we want to automatically add the character count of the post, in order not to exceed the limits of certain platforms.
We are adding the formula : len( {{Copy}} )



Example - 2 :

On our social media planning app, we have a publish date field. We want to create a deadline for the designer to get the image ready 3 days before the publish date, so we will create a new field as a date for the design deadline.
The formula we use : {{Publish Date}} - 3




Referencing fields:

In a Formula field, you can reference the fields on your list by using {{field_name}}.

If you want to calculate a 20% discounted price, the formula would look like this:

{{Price}} * 0.8


If you want to calculate the total price of an order:

{{Price}} * {{Quantity}}


You can combine multiple numeric operators by using parentheses:

( {{Price}} - {{Cost}} ) * 0.2



Logical Operators (Boolean type)

                                                                                                                                                                                                                
operator description example
and returns true if the both boolean operands are true 3>2 and 2>1   returns true
{{Price}} > 1000 and {{Cost}} < 800
or returns true if at least one of the boolean operands is true 3< 2 or 4<5  returns true
{{Price}} < 1000  or  {{Cost}} >800
not Returns true if the operand is not true !IsBlank(“Text”) 
equal Returns true if the both sides of the equation are the same {{Price}} = 1000
not equal Returns true if the both sides of the equation are not the same {{Price}} <> 1000
greater than Returns true if the first numeric value is greater than the second numeric value {{Price}} > 1000
less than Returns true if the first numeric value is greater than the second numeric value{{Price}} < 1000
greater than or equal  Returns true if the first numeric value is greater than or equal to the second numeric value {{Price}} >= 1000
less than or equal Returns true if the first numeric value is less than or equal to the second numeric value{{Price}} <= 1000


Logical Functions

                                                           
function description example
IF(logical_argument, return_if_true, return_if_false)  It assesses the logical argument and returns the first given value if true and second given value if false. IF(NOW() > {{Meeting_Date}}, “Meeting has past”, “Meeting is planned” )
IF(AND(logical1, logical2), return_if_true, return_if_false) 
IsBlank(field_value) Returns boolean value, true if the value is blank, false if not.  Null, empty string, string with only white space are considered as blank.isblank(\"\")  :  ExpectedResult = true
isblank(\"  \")  :   ExpectedResult = true
isblank(\" a \")  :   ExpectedResult = false
isblank(null)  :   ExpectedResult = true


Numeric Operators

                                                                                                                                               
operator description example
plus Add together two numeric values {{Cost}} + {{Shipment Fee}}
minus Subtract two numeric values {{Price}} - {{Cost}}
multiply Multiply two numeric values {{Quantity}} * 3
divide Divide two numeric values {{Total Price}} / 2
mod Returns the remainder after dividing the first operand with the second {{Distance}} % 1000
power Power of a numeric value     {{Quantity}} ^ 2


Numeric Functions

                                                                                                                                               
function description example
AVG(number1, number2, number,3)  Returns the average of given numbers. avg(3, 4, 5 )  :  ExpectedResult = 4
avg(null, 4, 5 )  :  ExpectedResult = 4.5
avg(0, 4, 5 )  : ExpectedResult = 3
MAX(number1, [number2, ...]) Returns the largest of the given numbers.MAX(990, 1000) = 1000
MAX( {{Price}}, 1000)  shows the price if it is greater than 1000, shows 1000 otherwise.
MIN(number1, [number2, ...]) Returns the smallest of the given numbers.MIN(990, 1000) = 990
MIN( {{Price}}, 1000)  shows the price if it is smaller than 1000, shows 1000 otherwise.
SUM(number1, [number2, ...]) Returns the sum of the given numbers. SUM(30, 20, 10) = 60
ROUND(number, digits)  Prefix operation that returns the Round of decimal numbers with specified number of digits.ROUND(4.23121, 2) = 4.23
COALESCE(operand1, operand2, operand3...) Prefix operation that returns the first non-null operand. COALESCE(null, null, 1, 2, 3,)  = 1


Text Operators

                                      
operator description example
+  (plus) Concatenates two texts values or adds other values into a text value such as numeric fields and returns one text value{{First Name}} + {{Last Name}}
“Hello” + {{First Name}}
“The remaining time is” + {{Remaining Time}} 
{{Project Name}} + “ - “ + {{Start Date}}


Text Functions

                                                                                                                                                                                                                                          
function description example
LEN(string) Returns the number of characters in a string. LEN( {{Description}} ) 
LEFT(string, number)  Extracts the specified number of characters from the beginning of the string.LEFT(“Hello World” , 5)  = “Hello”
RIGHT(string, number)  Extracts the specified number of characters from the end of the string.RIGHT(“Hello World” , 5)  = “World”
Text(number) Transforms a number field into a text field.{Text(1)  :   ExpectedResult = "1"
ToNumber(text) Transforms a text field into a number field.toNumber(\"44\")  :   ExpectedResult = 44
SUBSTRING(string, index) Returns the rest of the string as a substring after the given index number.Substring("12345678", 3)  :   ExpectedResult = "45678" Substring("example@gmail.com", 8)  :   ExpectedResult = "gmail.com"
INDEXOF(string, string)  Returns the location of the second string within the first string as the index number.IndexOf(“example@gmail.com” , "@")  = 9 IndexOf(“1234567” , "2")  = 1 IndexOf(“12345678” , "XYZ")  = -1 (does not exist)
REPLACE(string, string, string) Finds the second string within the first string and replace it with the third string.Replace("Hello World", "ll", "xx")  :   ExpectedResult = "Hexxo World" Replace("1-2-3-4", "-", ",")  :   ExpectedResult = "1,2,3,4"
TRIM(string) Removes the empty spaces from the beginning and the end of the string.Trim(" Hello ")  :   ExpectedResult = "Hello"
TRIMLEFT(string) Removes the empty spaces from the beginning of the string.Replace(" Hello ")  :   ExpectedResult = "Hello "
TRIMRIGHT(string) Removes the empty spaces from the end of the string.Replace(" Hello ")  :   ExpectedResult = " Hello"


Date and Time Functions

                                                                                                                                                                                                                
function description example
Now()  Returns the date of today. If the ‘Add Time’ option is selected, it also returns the time. Now() - {{Start Date}}
Month(date) Returns the month of the given date as a number. month(\"2015-5-1\")  :  ExpectedResult = 5)]
MonthName(number) Returns the name of the month as a text.MonthName(1)  : ExpectedResult = “January”
MonthName(6)  : ExpectedResult = “June”
ShortMonthName(number) Returns the shortened version of month name as a text. ShortMonthName(1)  :  ExpectedResult = "Jan"
Year(date)  Returns the year of the given date as a number. Year(\"2015-1-1\")  :   ExpectedResult = 2015
Format(Date(date_value), new_format) Transforms the date value to a given format. Format(Date(\"2015-2-10\"), \"yyyy-MM-dd\")  :   ExpectedResult = "2015-02-10"
Format(CreateDate(year, month, day), date_format) Transforms the given year, month, day numbers into a date field.Format(CreateDate(1985, 5,27), \"yyyy-MM-dd\")   :   ExpectedResult = "1985-05-27"
SubtractDate(date1), date2)) Subtracts the given date values to return the number of days between them.SubtractDate(\"2015-2-10\", \"2015-2-4\")   :   ExpectedResult = 6
SubtractDate(CreateDate(2020, 1, 4), CreateDate(2020, 1, 1))   :   ExpectedResult = 3
SubtractTime(datetime1, datetime2)Subtacts the given date-time values to return the minutes between them.SubtractTime(\”2015-2-10 11:15:00\”, \”2015-2-10 10:14:00\”)   :   ExpectedResult = 61
SubtractTime(CreateDate(2020, 1, 1, 11, 15, 0), CreateDate(2020, 1, 1, 10, 14, 0))   :   ExpectedResult = 61




Search icon

Looking for something else?

Search by entering some keywords such as; 'email automation', 'linked list'...
Email icon

Still need help?

If you could not find the answer to your question, please contact the support team using the chat box.