In most formulas, managing empty columns is essential to avoid errors. With the monday.com Formula column, it can be pretty complex. The Advanced Formula Booster offers much simpler options. Let’s take a look at a practical example to see the difference.
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).
A Practical Example
I dedicate a few minutes each day to assist users with the Formula column on the monday Community forum. Sometimes the question is complex and answering it is a challenge. Like this one: Calculating hours between 2 date/time columns. Took me a couple of minutes to figure it out and a few more to write an understandable explanation. In the end, the solution is:
(WORKDAYS(FORMAT_DATE({Resolved On},“YYYY-MM-DD”),FORMAT_DATE({Reported On},“YYYY-MM-DD”)–1)*8 + (TIMEVALUE({Resolved On}) – TIMEVALUE({Reported On}))*24
It calculates the number of working hours between the {Reported On} date and the {Resolved On} date. The only problem is that the infamous red icon pops up on every line with no date entered.
How do we get rid of them? Sincerely, I don’t know. I tried without success. I am sure it is possible by adding and repeating several if statements but the complexity of the formula is such it rapidly becomes difficult to manage.
Eager vs Lazy Approach in IF Statements
The main problem is that monday uses an eager approach to IF statements. An IF statement is structured this way: IF(Condition,Result_If_Condition_Met,Result_If_Condition_Not_Met). The eager approach is to calculate both results, then evaluate the condition and return the result that meets the condition.
The lazy approach is to evaluate the condition first then calculate only the appropriate result.
Why does it matter? In error handling, you typically want to add an IF statement to avoid calculating one of the results because it generates an error. But the eager approach makes it useless because no matter the condition, both results are calculated (one of them for nothing).
Handling errors with the STOP() function in the Advanced Formula Booster
Here’s how I would approach the formula using the Advanced Formula Booster. As usual, I am voluntarily verbose when writing code so that it is easy to understand (I am fully aware that this could be achieved in a more succinct way).
The first line checks if either the {Reported On} and {Resolved On} columns are empty and stores the info (true or false) in the [ShouldSkip] variable.
- In case one of them is empty, the instruction on line 2 blanks out the target column – a Number column called {Time Spent} – and the one of line 3 stops the execution of the formula.
- In case they aren’t, lines 2 and 3 are skipped (note the use of the IF statement with a single parameter so nothing happens if [ShouldSkip] is false).
- The “real” calculation starts on line 4.
1: [ShouldSkip]=OR({Reported On}=””,{Resolved On}=””)
2: {Time Spent}=IF([ShouldSkip],””)
3: [Stop]=IF([ShouldSkip],STOP())
4: [FullDayCount]=COUNTWORKINGDAYS({Resolved On},{Reported On})
5: [FullDayHours]=MULTIPLY([FullDayCount],8)
6: [ResolvedTime]=TIME({Resolved On})
7: [ReportedTime]=TIME({Reported On})
8: [MinutesDiff]=MINUTES([ResolvedTime],[ReportedTime])
9: [HoursDiff]=DIVIDE([MinutesDiff],60)
10: {Time Spent}=SUM([FullDayHours],[HoursDiff])
NOTE: If additional calculations beyond line 10 was required, the use of SKIP() and RESUME() functions would be more appropriate than STOP(). They’d allow for the continuation of the formula’s execution after line 10.
Testing Made Easy Thanks to the Formula Editor
Because the Advanced Formula Booster incorporates testing within the Formula Editor, you can easily visualize the outcome of each line. This is particularly interesting in this case scenario.
