Calculate date based on dependency date plus number

We would like a date of a dependent item to be able to be automatically calculated by taking the date of its dependency item and adding a number from the dependent item.

Old scenario:

Everyone adds their tasks to a spreadsheet, specifying how many days it will take in another column called “effort” - tasks are added in the order they are done in and every task is assumed to be dependent upon the previous. The project manager adds the completion date of the first item and a formula automatically calculates the completion date of all of the following items by adding their “effort” number to the date of the previous item. Then import to Monday.com.

Current scenario:

Everyone submits their list of tasks, specifying how long it will take and what it is dependent upon. The project manager then manually adds the completion dates to each item by taking the end date of the previous item (the item it is dependent upon) and adding the number of days effort of the current item.

Both scenario’s:

We use the automation “adjust the {date} of an item to reflect the changes made in the date of its {dependency} item” to ensure that if the date changes on 1 item, all dependent items have the same change reflected in their dates.

The old scenario works okay, but ironically still requires the tool we’re replacing with Monday.com for this particular use-case and without some extra work put in to the spreadsheet, assumes that every task is dependent on the previous task. The new scenario works well but when the project is quite big (has a lot of tasks), the project manager often reverts to using the old method.

Possible solution:

A new automation with the recipe of:

When dependency is added, set the items {date} to the date of its {dependency} item plus a {number} of days.

A potential challenge would be non-working days; it would be good if there could be an option to exclude weekends and public holidays when calculating the date? So that if something takes 3 days effort, and the dependency item is completed on Friday, it will finish on Wednesday instead of Monday.

Thanks,
Josh