With the Formula column, you can create anything from simple mathematical equations to more complex and advanced calculations using the data on your board. In this article, you will find all of the available formula functions that we offer on this column, let's check them out! ⬇️

 

 

Important to know

FORMULA FIELD REFERENCE

Column names should be wrapped in braces: SUM({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, World Clock

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 so 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: ISOWEEKNUM
Description: Returns the yearly week number of a given date according to ISO standards.
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”

 

Formula Use Cases

The possibilities with the Formula Column are endless! To get an idea of how the Formula Column can work for you, check out our article on Formula Use Cases. You can also find some of our most common use cases for the Formula column in the below board:

 

 

If you have any questions, please reach out to our team right here. We’re available 24/7 and happy to help.