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
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:
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: