Hands holding smartphone displaying logo of monday work management.
Image: monticellllo/Adobe Stock

Move over, Microsoft Excel: In monday work management, you can perform calculations and manipulate data through the platform’s Formula Column feature. Using the monday.com Work OS formula functions, you can easily solve mathematical formulas by pulling data from columns or as standalone calculations. These capabilities allow you to get solutions to formulas faster and without requiring more than one application so you can easily harness your data to its fullest potential within one system.

SEE: monday.com Work OS review (TechRepublic)

This tutorial walks you through how to use formulas in monday work management, which was rated on TechRepublic as the best project management software for teams of all technical levels.

What you need to use formulas in monday work management

In order to add or use formula columns, you need to have an active monday.com Work OS account through a Pro or Enterprise plan.

Note: If you have a Pro plan and downgrade your account in the future, you will not lose the formula columns you have created.

How to add formulas in monday work management

To begin adding formulas, you will need to add the Formula Column to your board by selecting the + icon located at the top right of your board chart (Figure A).

Figure A

Add the Formula Column to your board by selecting the + icon located at the top right of your board chart.
Image: Madeline Clarke/TechRepublic

In the drop-down menu that appears, click Formula (Figure B).

Figure B

In the drop-down menu that appears, click Formula.
Image: Madeline Clarke/TechRepublic

After adding your Formula Column, a formula builder will open on your page (Figure C). You can always access the formula builder by clicking within the column.

Figure C

A formula builder will open on your page.
Image: Madeline Clarke/TechRepublic

Now that you’re in the formula builder, you can start creating your formula. On the board, you will see a list of the columns that you can use in your formulas, as well as a list of functions that monday work management offers (Figure D). You can use any of these functions to build your formula, or you can simply use traditional math symbols like + to add, – to subtract, or * to multiply.

Figure D

A list of the columns that you can use in your formulas.
Image: Madeline Clarke/TechRepublic

If you want to apply functions, monday.com makes it easy to understand the purpose of each function. Hover your mouse over a function to see a description of the function and an example of its application (Figure E).

Figure E

Hover your mouse over a function to see a description of the function and an example of its application.
Image: Madeline Clarke/TechRepublic

To build a formula, begin by selecting a cell within the Formula Column where you can start to type your formula. Then, you can use columns from your board and functions as you create your formula.

For example, if you want to see whether your organization’s spending is over or under budget, you can apply the following formula:

IF({Budgeted}>{Actual Spending},"Over budget","Under budget")

Another benefit of using monday work management is that the software uses syntax highlighting to help you easily construct the formulas; you can see this as specific segments of your formula are colored within the area.

For example, if a segment within the formula you created is red, that indicates there is an error within that part of your formula. Keep an eye out for this, as it can help you realize and resolve mistakes within your formula.

Tips and tricks for using formulas in monday work management

As with any software system that uses formulas, you must follow specific rules to ensure proper formula usage and to produce accurate calculations.

To reference a column and pull its value into your function, add the name of that column exactly as it appears on your boards within these braces symbols {} in your formula (Figure F). Additionally, formulas can include parentheses () to change the order of operations.

Figure F

To reference a column and pull its value into your function, add the name of that column exactly as it appears on your boards within these braces symbols {} in your formula.
Image: Madeline Clarke/TechRepublic

Remember that values are case-sensitive, and when writing out your formula, you should capitalize words appropriately as they appear within your column.

If you choose to add commas in your formula columns, be careful, as they can represent something different depending on the function. For example, the comma would represent a multiplication symbol in a multiply function.

Always open and close statements by including a ( symbol before the statement to open it and a ) symbol after the statement to close it.

In formulas, text must be added in quotation marks in order for the software to distinguish it. Your quotation mark and comma formatting can also impact the effectiveness of your formula, and copying and pasting formulas from an external text editor may result in the formula builder reading it as illegal.

Finally, always include a 0 before the decimal when multiplying; otherwise, the formula will return an error.

The Formula Column lets you generate formulas based on compatible and incompatible columns.

Compatible columns: Check, Country, Creation Log, Date, Dependency, Dropdown, Email, Formula, Hour, Item ID, Last Updated, Connect Boards (previously Link to Item), Long Text, Numbers, Person, Phone, Rating, Status, Text, Timeline, Time Tracking, Vote, World Clock, Subitem Names and the Count of Subitems.

Incompatible columns: Autonumber, Color Picker, Files, Link, Location, Mirror, Progress Tracking, Tags and Week.

List of common formulas for monday work management

DATE

Provides the value for the given parameters

Example: DATE(2019,4,29) => Wed April 4 2019

DAY

Provides the day of the month of a given date.

Example: DAY({created at}) => 11

DAYS

Provides the number of days between the two dates

Example: DAYS({end date column name}, {start date column name}) => diff between dates in days

FORMAT_DATE

Provides a formatted date

Examples:

FORMAT_DATE(TODAY()) => "April 22, 2017" (using default format)

FORMAT_DATE(TODAY(), "YYYY-MM-DD") => "2017-04-22"

FORMAT_DATE(TODAY(), "dddd, MMMM Do YYYY") => Saturday, April 22nd 2017>

HOUR

Provides 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)

HOURS_DIFF

Provides the difference between two hours columns

Example: HOURS_DIFF("18:00", "23:00") => 05:00

ADD_DAYS

Adds days to the specified date, and provides the new date.

Examples:

ADD_DAYS("2020-02-22", 6)

FORMAT_DATE(ADD_DAYS("2020-02-22", 6) => "2020-02-28

WORKDAYS

Provides the number of working days between the two dates

Example: WORKDAYS({TO_DATE}, {FROM_DATE}) => working days between dates in days

MINUTE

Provides the minute as a number from 0 to 59.

Example: MINUTE(NOW()) => 17 (in case it's 23:17 now)

MONTH

Provides the month of a given date.

Example: MONTH({created at}) => 7

REPLACE

Replaces a segment of a string with the new string.

Example: REPLACE("Stay",1, 2,"Pl") => "Play"

RIGHT

Removes a number of characters from the right side of a text string.

Example: RIGHT("wednesday", 3) => day

SEARCH

Searches for a string within another string

Example: IF(SEARCH("am", "I am sad", 1) > 0, "Exist", "Not") => "exist"

SUBSTITUTE

Replace text in a provided text string by matching.

Example: SUBSTITUTE("yesterday", "day", "to") => today

REPT

Repeats a string a specified number of times.

Example: REPT("cat",4) =>catcatcatcat

SECOND

Provides the second as a number from 0 to 59.

Example: SECOND(NOW()) => 18 (in case it's 23:00:18 now)

SUBTRACT_DAYS

Subtracts the days from the given date. Provides the new date.

Examples:

SUBTRACT_DAYS("2018-11-08", 3)

FORMAT_DATE(SUBTRACT_DAYS("2018-11-08", 3) => "2018-11-05

TODAY

Provides the current date

Example: TODAY() =>01.01.2022

WEEKNUM

Provides the yearly week number of a given date.

Example: WEEKNUM({created at}) => 27

ISOWEEKNUM

Provides the yearly week number of a given date according to ISO standards.

Example: WEEKNUM({created at}) => 39

YEAR

Provides the year of a given date.

Example: YEAR({created at}) => 2022

CONCATENATE

This combines text values into a single text value

Example: CONCATENATE(“I”,”am”,”happy”) => Iamhappy

AVERAGE

Provides the average of the given numbers

Example: AVERAGE(1,3,5) => 3

DIVIDE

A number divided by another

Example: DIVIDE(9,3) => 3

COUNT

Provides the number of numerical items.

Example: COUNT(7, 3, "a") => 2

LOG

Provides the logarithm of a number

Example: LOG (9,2) => 3

MAX

Provides the largest value from a set of data

Example: MAX(2,1,19,12) => 19

MIN

Provides the smallest value from a set of data

Example: MIN(11,42,62,19) => 11

MINUS

Provides the difference of two numbers

Example: MINUS(7,4) => 3

MULTIPLY

Multiplies two numbers

Example: MULTIPLY(2,7) => 14

MOD

Provides the remainder of the division of the number in the divisor

Example: MOD(16,5) => 1

ROUND

Rounds a number to a number of digits

Example: ROUND(5.91268456, 2) => 5.91

ROUNDUP

Rounds a number up

Example: ROUNDUP(8.2,0) => 9

ROUNDDOWN

Rounds a number down

Example: ROUNDDOWN(8.2,0) => 8

SQRT

Provides the positive square root of a positive number

Example: SQRT(16) => 4

SUM

Provides the sum of all the given numbers

Example: SUM(7,1,4) => 12

LEFT

Removes a given number of characters from the left side.

Example: LEFT("happy", 2) => ppy

LEN

Provides the number of characters of a given text string.

Example: LEN("welcome") => 7

LOWER

Transforms a specified string to lowercase

Example: LOWER("Left TURN") => "left turn"

TEXT

Formats the given value based on the given text format

Example: TEXT(7600.5,"$#,##0.00") => $7,600.50

TRIM

Extracts all spaces from a string except for single spaces between words.

Example: TRIM(" I hate beets ") => "I hate beets"

UPPER

Transforms a specified string to uppercase

Example: UPPER(“hello”) => HELLO

AND

Inspects if all given logical conditions are true and if so returns true

Example: AND(2>1,8>5) => True

EXACT

Analyzes two values, returns true if they are the same and false if they are different.

Example: EXACT({Status},{Status})) =>TRUE

IF

Inspects if a condition is met. If so, returns the first value, if not, returns the other

Example: IF({some columns}>100, “big deal”,”small deal”)

OR

Answers true if any one of the argument is true

Example: OR(4 >5, 8>6) => True

XOR

Provides a logical exclusive Or of all the arguments

Example: XOR(3>0, 2>9) => True

SWITCH

Determines if a condition on a value is met. If so, it returns the result of that value. Otherwise it returns the default result if it 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")

ABS

Provides the absolute value of a given number

Example: ABS(-1) = 1

POWER

Raises a number to a power

Example: POWER(4,3) => 64

PI

Provides the PI value 3.14159

Example: PI() => 3.14159

TRUE

Provides the logical true value

Example: IF(3>5 = TRUE, “a”,”b”) => “b”

FALSE

Provides the logical false value

Example: IF(3>5 = “FALSE”, “a”, “b”)=> “a”

If you want to manage software development projects and processes, this TechRepublic Academy course geared toward project management can help you take your career to the next level.