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
In the drop-down menu that appears, click Formula (Figure B).
Figure B
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
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
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
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
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-28WORKDAYS
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-05TODAY
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.