What can we help you with?

Formula Use Cases

The Formula Column is one of the more popular ways to manipulate data on monday.com. From simple mathematical calculations to more complex formulas utilizing our library of available functions, the Formula Column can simplify complex problems.

To get started, if you haven't had a chance to check out our introductory article on the Formula Column and the helpful tips and tricks there, you can visit this link to take a look.

Once you've had a chance to explore that, take a look at the use cases we've collected here. Below you'll find some of the most common ways to use the Formula Column.

 

 

Adding or subtracting days from a date

In this example, we want to add 15 days to each date from the column "Start Date": 

ADD_DAYS({Start Date},15) 

If we wanted to subtract 15 days instead, we would use the function SUBTRACT_DAYS() in place of ADD_DAYS().

SUBTRACT_DAYS({Start Date},15) 

Both of the above formulas will show you an unformatted result that may look a little clunky. This is why we recommend using the following formula. The result is formatted and will look cleaner on your board: 

FORMAT_DATE(ADD_DAYS({Start Date},15))

Frame_31.png

If you want to remove the year and simply see the month and day, you can further customize your formula by using the LEFT() function. In the following formula, the LEFT() function takes the output of the FORMAT_DATE() function and only outputs 6 characters from the left:

LEFT(FORMAT_DATE(ADD_DAYS({Start Date},15)),6)

If we change the 6 in the formula, we change the number of characters that the formula outputs.

The results of this formula can be seen in the "LEFT" column:

Frame_31__1_.png

 

Calculating days between a date and today

One really useful and dynamic function is TODAY(). Using this with the DAYS() function, you can calculate the number of days until the due date (or past the due date). When using TODAY(), you don't need to include anything within the parenthesis.

We've also encapsulated the output of the DAYS() function within the ROUND() function. In this case, we're indicating that we want to round the output with 0 decimal places.  

ROUND(DAYS({Due Date},TODAY()),0)

Frame_38.png

 

Determining the number of working days between two dates

If you're using a board to keep track of employee vacation requests, a formula can be useful when calculating the number of working days the employee will need. The following function, WORKDAYS(), will return the number of working days between two dates. Working days are defined, according to your account settings, as Monday to Friday or Sunday to Thursday. To learn more about this account setting, check out this article.

WORKDAYS({To},{From})

Frame_39.png

 

Calculating billable total using the time tracking column 

When using the Time Tracking Column with the Formula Column, you can choose whether you want to pull in seconds, minutes, or hours. For the following formula, we're using the "Billable" column's hours. We're also using the ROUND() function again to clean up our decimals. The number 2 in this formula signifies two decimal places.

ROUND(MULTIPLY({Billable#Hours},{Hourly Rate}),2)

Frame_32__2_.png

 

Totaling hours worked excluding breaks

In this example, I'd like to calculate how long my contractors have worked. This should not include their unpaid break time. Using four Hour Columns, I can create a formula to calculate this. 

IF(HOURS_DIFF({Break End}, {Break Start}) > "0", HOURS_DIFF(HOURS_DIFF({End}, {Start}), HOURS_DIFF({Break End}, {Break Start})), HOURS_DIFF({End}, {Start}))

This formula says that if the break is greater than 0, calculate the total hours worked minus the break. If the break is not greater than 0, calculate the total hours worked.

Here's how the formula works:

Frame_33.png

 

Calculating the change between two values

In this board, we're looking at the total sales per month for four employees. To calculate the change between the results of January and February as a percentage, you would use the following formula:

MULTIPLY(DIVIDE(MINUS({February Sales},{January Sales}),{January Sales}),100)

This formula can also be written as:

((({February Sales}-{January Sales})/{January Sales})*100)

Frame_35__1_.png

 

Outputting a value based on two conditions

Now we want to calculate each employee's bonus. An employee will receive a bonus only if the "Total Sales" are higher than $350,000 and if the number of deals in the "Deals" column is higher than 12:

IF(AND({Total Sales}>350000,{Deals}>12),250,0)

The AND() function checks whether the two conditions are true. Based on the result, the IF() statement tells the formula column which value to return.

Frame_36__3_.png

 

Using status labels to assign commission rates

In this example, let's say I manage a sales team with varying commission rates per salesperson.  You can use the labels from the status column to indicate a specific rate within your formula: 

IF({Rate}="Rate 1",25,IF({Rate}="Rate 2",20,IF({Rate}="Rate 3",15,IF({Rate}="Rate 4",10,IF({Rate}="Rate 5",5)))))

The "Commission %" column is showing the relevant rate based on the status label selected.

Frame_40.png

You can take this formula a step further and calculate commission based on the rate and the "Total Sales" column by incorporating the MULTIPLY() function. Just keep in mind that if you use a decimal in a formula, you must write 0.25 rather than .25 to avoid an illegal formula error.

IF({Rate}="Rate 1",MULTIPLY(0.25,{Total Sales}),IF({Rate}="Rate 2",MULTIPLY(0.20,{Total Sales}),IF({Rate}="Rate 3",MULTIPLY(0.15,{Total Sales}),IF({Rate}="Rate 4",MULTIPLY(0.10,{Total Sales}),IF({Rate}="Rate 5",MULTIPLY(0.05,{Total Sales}))))))

Frame_41__1_.png

 

Comparing a dynamic value to a static value

If you're using a board to keep track of your budget, this formula might come in handy. In this example, the travel budget for each employee is $6,500. We want to find out if the total amount spent on each employee is within the budget or over it. To do this, we will compare the SUM() of the values in four columns to the budget with an IF() statement.

IF(SUM({Flight},{Hotel},{Insurance},{Expenses})>6500, "Over Budget","Good")  

Frame_37__1_.png

 

Tracking inventory

You can easily manage your inventory with monday.com. This example explains how to calculate your current available stock and how much inventory was sold (as a percentage).

For the "In Stock" column:

MINUS(MINUS({Starting Inventory},{Reserved}),{Sold})

Frame_42.png

For the "% Sold" column:

ROUND(MULTIPLY(DIVIDE({Sold},{Starting Inventory}),100),2)

Frame_43__1_.png

 

Formatting a formula

By using the TEXT() function, you can format your results exactly the way you want. Let's take an easy example in the first line of the following board. We want to multiply 100 by 25, and we want the output to show like this: $2,500.00.

TEXT(MULTIPLY({Starting Inventory},{Cost}),"$#,##.00")

Frame_44__1_.png

In the last part of the formula, each # represents a number. We added .00 to the end because we want the output to end with two zeros, but you can replace this with .## if you want your output to end with calculated numbers. The $ places the symbol in front of the number.

Keep in mind that if you format a number using the TEXT() function, the formula column will read the number as text rather than a number. This means that your column summary will show "N/A" (as above) instead of the sum/average/etc. of the numbers shown in your formula column.

 

Additional use cases

Didn't find what you were looking for in this article? Not to worry! In this board, you will find some of our most common use cases:

 

 

Tip: To access our Formula Use Cases board, you can also click right here🙏

 

If you still have questions, we're here for you! Check out the Community FAQ forum on formulas, or reach out to us at [email protected] with any questions you may have.