When using the Formula column in monday.com, you are limited to manipulating values within the same item. The Advanced Formula Booster expands these capabilities, enabling the reading of values from either the Previous or Next item in the same group and allowing updates across any column of these items. Here’s a demonstration of leveraging this feature to cascade values between items.
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).
An Example of Cascading Values
Watch the video below. We input a Start Date in the 1st item, and it automatically calculates the End Date by adding the number of working days specified in the Duration column. Then, it increments this End Date by one working day and populates the next item’s Start Date column with this new date. This action initiates the End Date calculation for the 2nd item, which in turn triggers the Start Date calculation for the 3rd item, and so on.
Then we change one of the Duration values and it automatically recalculates all dates.
How do we achieve this? By creating a formula with 2 simple lines of code.
Two Simple Lines of Code are Enough
Here are the 2 lines of code:
1: {End Date}=ADDWORKINGDAYS({Duration},{Start Date})
2: {Next.Start Date}=ADDWORKINGDAYS(1,{End Date})
In the 1st line, we use the ADDWORKINGDAYS function to add the number of days specified in the Duration column to the Start Date.
The ADDWORKINGDAYS function accounts for the Days Off and Holiday settings configured in your mdBoosters account. The Days Off settings allows you to define your work week (e.g., Mon to Fri or Sunday to Thursday), while the Holiday settings enable you to input any country-specific holidays.
In the 2nd line, we increment the End Date by one working day and direct this updated date to the Start Date of the next item, indicated by adding the ‘Next’ prefix to the {Start Date} placeholder.
Automations required to trigger the formula
We need at least a couple of automations to make everything run smoothly.
Conclusion
By enabling the reading and writing of values across different items within the same group, the Advanced Formula Booster introduces another game-changing feature to monday.com formulas.
This enhancement significantly broadens the scope of formula capabilities, all the while maintaining ease of use.