What can we help you with?

List of all available formulas

 

Note: This article is a library of all currently available formulas. If you are looking for an explanation on how to use the formula column, check out this article.  😊

 

FORMULA FIELD REFERENCE

Column names should be wrapped in bracesSUM({apples},{oranges})

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

 

COMPATIBLE COLUMNS

Check, Country, Creation Log, Date, Dependency, Dropdown, Email, Formula, Hour, Item ID, Last Updated, Link to Item, Long Text, Numbers, Person, Phone, Rating, Status, Text, Timeline, Time Tracking, Vote

Subitem Names and the Count of Subitems are also supported.

 

INCOMPATIBLE COLUMNS

Autonumber, Color Picker, Files, Link, Location, Mirror, Progress Tracking, Tags, Week, World Clock

 

Text functions 

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

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

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

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

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

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

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

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

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

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

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

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

 

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: EXACT
Description: Compares two values, returns true if they are the same and false if they are different.
Example: EXACT({Status},{Status})) =>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: 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: SWITCH
Description: Checks if a condition on a specific value is met, if so, returns the result of that value, o/w returns the default result (if exists). The pattern is: SWITCH({Column}, "val1", "result1", ["val2", "result2"], ..., ["default"])
Example: SWITCH({Priority}, "High", 3, "Medium", 2, "Low", 1, 0) => 2 (in case priority is "Medium")

 

 Numeric functions

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

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

 

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

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

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

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

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

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: MOD
Description: Returns the remainder of the division of the given number in the divisor
Example:MOD(10,3) => 1

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

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: SQRT
Description: Positive square root of a positive number
Example:SQRT(9) => 3

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

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

 

Date and time functions

Function: ADD_DAYS
Description: Adds days to the given date. Returns the new date.  
Examples: ADD_DAYS("2019-01-20", 5)
ADD_DAYS(TODAY(), 5)
FORMAT_DATE(ADD_DAYS("2019-01-20", 5) => "2019-01-25

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

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

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: FORMAT_DATE
Description: Returns a formatted date
Examples: FORMAT_DATE(TODAY()) => "Feb 16, 2020" (using default format)
FORMAT_DATE(TODAY(), "YYYY-MM-DD") => "2020-02-16"
FORMAT_DATE(TODAY(), "dddd, MMMM Do YYYY") => Sunday, February 16th 2020

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: HOURS_DIFF
Description: Returns the difference between two hours columns
Example: HOURS_DIFF("23:00", "20:00") => 03:00

Function: WORKDAYS
Description: Returns the number of working days between the two dates
Example: WORKDAYS({TO_DATE}, {FROM_DATE}) => working days between dates in days

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: MONTH
Description: Returns the month of a given date.
Example: MONTH({created at}) => 7

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)

Function: SUBTRACT_DAYS
Description: Subtract days from the given date. Returns the new date.  
Examples: SUBTRACT_DAYS("2019-01-20", 5)
SUBTRACT_DAYS(TODAY(), 5)
FORMAT_DATE(SUBTRACT_DAYS("2019-01-20", 5) => "2019-01-15

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

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

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

 

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”