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! ⬇️

**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. We also have a Formula Use Cases Article available with lot's of great formula examples! 😊

**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 still have questions, we're here for you. You can reach out to our team by using our contact form. We're available 24/7 and happy to help!

## Comments