Welcome to the New Smartsheet Online Community
You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.
Formula for Actual duration excluding duplication
I am trying to calculate the actual duration starting from a list of Start and End dates.
What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous time ranges.
Please see example:
Start: 01/10/2019 End: 04/10/2019 = 4 working days effort October
Start: 01/11/2019 End: 15/11/2019 = 11 working days effort November
Start: 11/11/2019 End: 18/11/2019 = 6 working days effort November
Start: 15/11/2019 End: 22/11/2019 = 6 working days effort November
Start: 09/12/2019 End: 13/12/2019 = 5 working days effort December
Total effort = 32 days --> this is NOT what I need
Actual days = 4+11+1[=6-5 overlap]+4[=6-2 overlap]+5= 20 days --> this is what I need
Please consider that this requires to be completely automated through formula as it needs to be deployed into multiple very long backlogs.
I would really appreciate your help.