Do you like nesting functions? You may not know what nesting means, but if you’ve written formulas in monday.com you certainly have nested functions. Nesting means inserting a function inside another function. Trust me: no one likes nesting functions.
If you are not familiar with the Advanced Formula Booster app, designed to reinvent formulas in monday.com, you may want to read The basics of the Advanced Formula Booster. It walks you through the different steps of creating your first formula, then automating it (screenshots + video).
Nesting is the norm with the formula column
Here is a typical example of a formula which requires nested functions because you have to write your formula in one single instruction when you use the formula column. It is a real case we found in the monday Community Forum:
Not easy to follow, right? Because there are 4 levels of nesting. The 2nd IF function is nested inside an ADD_DAYS function which is inside a FORMAT_DATE function which is inside the first IF function.
Look at the number of parentheses! If you misplace one, the formula won’t work.
Can you figure out what the purpose of this formula is? If the call status reason column is set to “No answer”, it pretends to calculate the date for the next call, 30 days after a date specified in another column (the BFS Date column or, if empty, the Date of call column).
No nesting necessary with the Advanced Formula Booster
With the Advanced Formula Booster, you may write formulas in a single instruction as well. But why would you? You can achieve a better result, while ensuring everyone understands what is being calculated.
Here is one way to write the same formula. It is verbose, but we did it on purpose so that it would remain easy to understand.
1: [STOP]=IF({call status reason}<>”No answer”,STOP())
2: [D]={BFS Date}
3: [D]=IF([D]=””,{Date of call})
4: {Date of next call}=ADDWORKINGDAYS(20,[D])
In the first line, we check if the call status reason is set to “No answer”. If it is not, the formula is stopped. No need to go any further, right?
In the 2nd line, we set [D] (an arbitrary name to represent the date we want to add 30 days to) to the BFS Date.
In the 3rd line, we check if [D] is empty. If it is, we give it the value of the Date of call instead.
Now, before we add 30 days to it, what if 30 days after [D] is a week-end or a company holiday? The formula written with the formula column doesn’t try to determine if it is or not, because it can’t. But the Advanced Formula Booster can. Why? Because you can set your working week and your company holidays in the Days Off page of the App Center.
So, in the last line, instead of blindly adding 30 days, we prefer to add 20 working days, which is typically equivalent to 30 calendar days.
Conclusion
When you write the formula with the Advanced Formula Booster:
- You get a more appropriate result, i.e. a true working day based on your own calendar.
- The syntax is more user-friendly.
- The resulting date is stored in a date column, not in a formula column, which means you can reuse it anywhere in monday.com or in another formula, unlike when stored in a formula column.