Calculating timeline duration from subitems durations sum

[Yishay (Yishay Barlev)]

Hello,
I’d like to discover a way to calculate a timeline for each of our projects rather than having to fill (and later update) them manually.
In our board each item is a project, whereas subitems are separate tasks which the R&D team defines and estimates their duration in man-days. I started by creating a linked field in the project which sums up the duration of all its subitems:

However, the resulting field type isn’t a number according to Monday’s strong-type rules, so the timeline field refuses to take it to calculate its duration:

Does anyone knows of a way of accomplishing this? I saw a couple of paid automation options which I’m considering, but even with those I’ll have to go and calculate all the existing items in my board.
Any help will be much appreciated!

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi @Yishay,

You may want to look at the 3rd-party app called the Advanced Formula Booster for this.

The Advanced Formula Booster has many very unique features as you’ll see below, one of which is its capability to write formula results to any type of columns (it doesn’t use the formula column at all).

DEMO OF A FORMULA SOLVING YOUR ISSUE
Here is a quick demo:

For this, all you need is to create a formula at the sub-item level. Here is its syntax:

Explanation: the formula runs at the sub-item level every time you change the value of a duration column. What it does is sum up the {Durations (days)} columns of all the sub-items under the same parent. That’s what the ParentSubItems prefix means (the sub-items of the parent of the current sub-item, i.e. the one that triggers the formula) and writes the result into the parent’s Duration (days) column.

The Duration (days) column being numeric, it can be attached to the Timeline column, as requested.

For this, you only need 1 automation:

DEMO OF A FORMULA PUSHING IT FURTHER
Now, let’s say you want to push the concept further as I see you have a Due Date column at the project’s level. Let’s say we want to set the timeline from the Due Date backwards. And let’s say you want to only take into account business days. I added a Timeline 2 column and a Duration 2 column, but did not tie them together.

This is done with the same trigger but this new formula:

In Line 2, I store the total duration in [Total Duration] and write it to the Parent’s Duration 2 column in Line 3 (same as the previous formula up to here).

In Line 5, I take the Parent’s Due Date and subtract the total duration, using the ADDWORKINGDAYS function which only takes into account business days (in this case, Monday to Friday, but this can be customized). It gives me the date the project should start to finish at the due date.

In Line 7, I set the parent’s timeline so it goes from the calculated Start Date to the Due Date.

Hope it gives you a solution to your problem, as well as ideas on how to push it further!

[Yishay (Yishay Barlev)]

Thank Gilles, for the quick and detailed answer. What you’ve presented is exactly what I’m looking for. Sadly, for the moment I can’t use this paid-for plugin, so I’ll be looking for other options.
This issue highlights a huge drawback in Monday - I can do everything we both describe here easily with fields and formulas. What I miss is the ability is copy-paste the formula’s result to a non-formula field. Or better yet, for Monday to be able to treat a formula field as a number or a timeline or anything else.
Until that happens, if it ever does, what other plugins can be used to copy-paste results to a non-formula fields?
And is there any solution which doesn’t require a plugin? I really think that I’m not trying to do anything special here…