Many monday.com users create boards from a template, where tasks and sub-tasks come pre-populated. Once the board is added, they input the main task dates and hope the sub-task dates will auto-populate accordingly. That’s not always the case. This tutorial guides you on making that happen.
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).
The Scenario
Here is a scenario, not necessarily the most common one, but a challenging one we wanted to take on. We want to enter the task’s ‘End Date’ and have all sub-items’ Start and End Dates to fill themselves automatically.
Step 1: Create the Formula
Here is the syntax of the formula.
1: –Get the total duration of the sub-tasks
2: [TotalDuration]=INVERT(SUM({Sub.Duration}))
3: –Deduct it from the End Date to get the Start Date
4: {Start Date}=ADDWORKINGDAYS([TotalDuration],{End Date})
5: –Get the list of cumulative sub-tasks durations
6: [CumulDuration]=CUMULATIVESUM({Sub.Duration},True)
7: –Invert and Reverse the list
8: [CumulDuration]=INVERT([CumulDuration])
9: [CumulDuration]=REVERSE([CumulDuration])
10: –Apply to End Dates
11: {Sub.End Date}=ADDWORKINGDAYS([CumulDuration],{End Date})
12: –Calculate Start Dates by deducting Durations from End Dates
13: {Sub.Start Date}=ADDWORKINGDAYS(INVERT({Sub.Duration}),{Sub.End Date})
The first 4 lines are about calculating the main task’s ‘Start Date’.
- First, we calculate the total duration of the task. For this, we use the {Sub.Duration} column placeholder. Since there is more than 1 sub-item, we get the list of durations separated by the list separator (3|2|5|2|1). We obtain the total using the SUM() function (13) and invert the result (-13).
- Now we calculate the ‘Start Date’ by using the ADDWORKINGDAYS() function. Since the number of days is negative, the function deduces 13 working days from the ‘End Date’ to get the ‘Start Date’.
The other lines are about calculating the sub-tasks’s ‘End Date’ and ‘Start Date’ values.
- Line 6 gets the cumulative sum of the durations. That’s the key part. We have durations of 3|2|5|2|1 days. By applying the CUMULATIVESUM() function, we obtain: 0|3|5|10|12, i.e. the sum of all preceeding elements (not including itself).
- Then we invert this list and reverse it: we obtain -12|-10|-5|3|0.
- That’s the list of durations we need to substract to the main task’s End date to get each sub-item’s end date (since we inverted the list, we are actually deducting days, i.e. going backwards).
- Now that we have the End Dates for all sub-tasks, it is easy to get the Start Dates. We simply deduct the durations using the ADDWORKINGDAYS function.
Step 2: Run a simulation
Here is the simulation we run using April 30, 2024 as End Date.
Step 3: Add 1 Automation to the Automation Center
When ‘End Date’ changes, we want the formula to run, so we only need to create 1 automation.
Step 4: Test
Conclusion
With the Advanced Formula Booster, it is easy to automate your monday.com boards, including when multiple items (sub-items here, but the same can be applied to other items) need to be updated.