Calculate period duration

menschikov · May 19, 2022, 5:43am

Hello there! Can anyone please help me with formula for next task:

I have a table with some problem periods in my software (full and partial downtimes are there) - see the image.
In this table I have start time, end time and duration of the downtime
sometimes these problem periods are intersect (e.g in case of partial downtimes: feature A downtime intersects with feature B downtime), even there can be full overlapping (3-4 problems\rows with same start and end date)

I need to calculate in another table total duration of problem in every month
The problem is I can’t just sum the durations because there’s mass intersections (not just 2 but 3+ rows), if I just sum durations I can have total problem days more than days in the months - impossible

Another issue is a problem can start in one month and and in another - I need to split its duration and allocate parts to correct month

Uploading: Screenshot 2022-05-19 at 10.42.47.png…

Tanks in advance!

Christiaan_Huizer · May 20, 2022, 7:14am

hi @menschikov ,
it is an interesting puzzle, but I am afraid a detailed answer extends the capacity of the community. I would suggest to apply the main principles:

filtering on product (and maybe on product & month)
splitting the months

First the filtering, this is check where values on this row live in the table:

The splitting of the month in a few steps:

showing the months via List(), but note this is just for the insight, not for the formulation of the solution

we get the end dates of each month

from here we do take the difference:

You might need quite a bit more I guess to make it work, like adding buttons to push the data in new tables, etc. That I consider out of reach for the community, it is tailor made work.

Anyway I hope this helps so far and you might find some inspiration here as well:

Enjoy!