The Formula Library - List of all available formulas

This article is a library of all the current available formulas. If you are looking for explanation on how to use it, check out this article 😊

FORMULA FIELD REFERENCE

Column names with multiple words should be wrapped in braces: SUM({apples},{oranges})

Formulas may include parentheses () to change the order of operations: (Apples + Oranges) / Guests 

AVAILABLE COLUMNS

Text, Status, Person, Numbers, Date, Rating, Vote, Check, Country, Creation Log, time tracking. 

FUNCTIONS

• Text functions 

Function: CONCATENATE
Description: This operator concatenate text values into a single text value
Example: CONCATENATE(“I”,”love”,”monday.com”) => Ilovemonday.com

Function: UPPER
Description: Convert a specified string to uppercase
Example: UPPER(“monday.com”) => MONDAY.COM

Function: LOWER
Description: Converts a specified string to lowercase
Example: LOWER("Some STRING") => "some string"

Function: TEXT
Description: Formats the given value based on the given text format
Example: TEXT(8500.6,"$#,##0.00") => $8,500.60

Function: REPLACE
Description: Replaces a part of a string with the new string.
Example: REPLACE("Goat",1, 2,"Fl") => "Flat"

Function: SUBSTITUTE
Description: Replace text in a given text string by matching.
Example: SUBSTITUTE("goodmorning", "morning", "night") => goodnight

Function: SEARCH
Description: Searches a string within another string
Example: IF(SEARCH("love", "I love monday", 1) > 0, "Exist", "Not") => "exist"

Function: LEFT
Description: Extracts a given number of characters from the left side.
Example: LEFT("monday.com", 3) => mon

Function: RIGHT
Description: Extracts a number of characters from the right side of a given text string.
Example: RIGHT("monday", 3) => day

Function: LEN
Description: Returns the amount of characters of a given text string.
Example: LEN("hello") => 5

Function: REPT
Description: Repeats a string a given number of times.
Example: REPT("monday",3) =>mondaymondaymonday

Function: TRIM
Description: Removes all spaces from a string except for single spaces between words.
Example: TRIM(" I love MDY ") => "I love MDY"

• Logical functions

Function: AND
Description: Checks if all the given logical conditions are true and if sop returns true
Example: AND(3>1, 4>2) => True

Function: OR
Description: Returns true if any one of the argument is true
Example: OR(3 >10, 4>2) => True

Function: XOR
Description: Returns a logical exclusive Or of all the arguments
Example: XOR(3>0, 2>9) => True

Function: IF
Description: Checks if a condition is met. If so, returns the first value, otherwise returns the other
Example: IF({some columns}>100, “big deal”,”small deal”)

Function: EXACT
Description: Compares two values, returns true if they are the same and false if they are different.
Example: EXACT({Status},{Status})) =>TRUE

• Numeric functions

Function: SUM
Description: Sums up all the given numbers
Example: SUM(2,3,8) => 13

Function: MINUS
Description: Difference of two numbers
Example:MINUS(5,3) => 2

Function: MULTIPLY
Description: Product of two numbers
Example:MULTIPLY(5,2) => 10

Function: DIVIDE
Description: One number divided by another
Example: DIVIDE(10,5) => 2

Function: AVERAGE
Description: Returns the average of the given numbers
Example: AVERAGE(1,2,3) => 2

Function: COUNTS
Description: Counts the number of numerical items
Example: COUNT(1,2,"a") =>2

Function: ROUND
Description: Rounds a number to a specific number of digits
Example: ROUND(1.123456, "2) => 1.12

Function: MOD
Description: Returns the remainder of the division of the given number in the divisor
Example:MOD(10,3) => 1

Function: ROUNDUP
Description: Always rounds a number up
Example: ROUNDUP(1.1,0) => 2

Function: ROUNDDOWN
Description: Always rounds a number down
Example: ROUNDDOWN(1.1,0) => 1

Function: LOG
Description: Gets the algorithm of a number
Example: LOG (16,2) => 4

Function: MIN
Description: Returns the smallest value from a set of data
Example: MIN(1,3,5,9) => 1

Function: MAX
Description: Returns the largest value from a set of data
Example: MAX(1,3,5,9) => 9

Function: SQRT
Description: Positive square root of a positive number
Example:SQRT(9) => 3

Function: POWER
Description: A number raised to a power
Example: POWER(2,3) => 8

Function: ABS
Description: Returns the absolute value of a given number
Example: ABS(-1) = 1

• Date and time functions

Function: TODAY
Description: Gets the current date
Example: TODAY() =>05.07.2018

Function: DATE
Description: Returns the value for the given parameters
Example: DATE(2018,5,30) => Wed May 30 2018 

Function: DAYS
Description: Returns the number of days between the two dates
Example: DAYS({end date column name, start date column name}) => diff between dates in days

Function: YEAR
Description: Returns the year of a given date.
Example: YEAR({created at}) => 2018

Function: MONTH
Description: Returns the month of a given date.
Example: MONTH({created at}) => 7

Function: WEEKNUM
Description: Returns the yearly week number of a given date.
Example: WEEKNUM({created at}) => 42

Function: DAY
Description: Returns the day of the month of a given date.
Example: DAY({created at}) => 31

Function: HOUR
Description: Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Example: HOUR(NOW()) => 23 (in case it's 23:00 - 23:59 o'clock now)

Function: MINUTE
Description: Returns the minute as a number from 0 to 59.
Example: MINUTE(NOW()) => 10 (in case it's 23:10 now)

Function: SECOND
Description: Returns the second as a number from 0 to 59.
Example: SECOND(NOW()) => 1 (in case it's 23:00:01 now)

• Constants

Function: PI
Description: PI value (3.14159)
Example: PI() => 3.14159

Function: TRUE
Description: Logical true value
Example: IF(3>5 = TRUE, “a”,”b”) => “b”

Function: FALSE
Description: Logical false value
Example: IF(3>5 = “FALSE”, “a”, “b”)=> “a”