I would like to submit an additional approach using more helpers to get a clear structure. This is, of course, not intending to depreciate any suggestion already posted! ask50705DurationPerDay001.ods
(Editing with regard to the changes and completions of the question made by updating.)
I’m not convinced there’s much sense in it. The summing of “intersecting durations” per day is, however, feasible. Overlapping of tasks might need to be excluded.
ask50705DurationPerDay002.ods
=== Edited with regard to the solution posted by the questioner ( @maurice ) himself, concerning an alternative solution without helpers===
I don’t like nested IFs too much and I wouldn’t call the given formula exactly simple, but I could verify it under the obvious assumptions.
There is, however, in principle a solution to the Interval Intersection Task “hiding” the few inevitable IFs in calls of the functions MIN and MAX. I would like to explain this.
Let’s assume the intervals are - not regarding the inclusion of the limits - (a,b) and (A,B) with a<=b and A<=B assured. The intersection - if not empty - will then be an interval (L,U) for witch we have to find the limits L and U and the length d=U-L if U>L . If the inteval is empty we won’t get specific limits, but we can describe it by any pair (L,U) with L>U. For the Length we then have to set d = MAX(U-L,0) to get 0 for empty intervals. For nonempty intersection
L = MAX(a,A) and U = MIN(b,B) hold, and we get, whether or not the intersection is empty:
d = MAX( MIN(b,B) - MAX(a,A) , 0)
If we omit the above assurances and take the given intervals to denote the empty set if a>b or A>B respectively, we will get the then correct intersection length 0 in every such case.
Solving the questioners problem, however, we need to add an unknown number of intersection lengths for a single day. This should be done generating the intersections by an array formula under “iterative” evaluation and adding the results. Let’s name the ranges of the StartDateTimes and the EndDateTimes of the tasks aRange and bRange respectively and the start (midnight) of the day under evaluation A, the end of the same day B (=A+1), then we might get the total duration of tasks intersecting with this day by
{=SUM(MAX(MIN(bRange,A+1)-MAX(aRange,A),0)) and this looks pretty simple.
Alas! Every one of the three standard functions used here is one that accepts lists for its parameters - and this means it cannot return arrays by the kind of “iteration” we need. With regard to SUM the solution is very simple. We substitute SUMPRODUCT and get for free the advantage that it has ‘ForceArray’ paramteres and we needn’t even enter the formula explicitly in array mode. For MIN and MAX, however we have to find substitutes. The easiest way to do so is using a few IFs again - and an addtional trick:
=SUMPRODUCT((IF(bRange<A+1;bRange,A+1) - IF(aRange>A;aRange;A)*(IF(CURRENT()<=0;0;1)) where the usage of CURRENT substitutes the final MAX -that is the trick - can help to get better efficiency . I did not find a way to enhance efficiency even more. We still have to accept that the first two IF calls will need a second evaluation of their ranges if the contition comes out TRUE.
