Note: 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 logarithm 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”
Comments