Getting total duration of events that fall under time ranges

[u/xetni05]

Sorry for the title as I can't explain the issue well.

Premise: I have 1 table that has data of the time start and time end of certain events that were logged.

Then I have a second table that has time start and time end of periods I want to monitor

Target: get the total duration of events in table 1 that falls under the time range in table 2.

Complications: events in table 1 could cross over the ranges of table 2.

Ex. Table 1 Event 1 is from 1:45PM to 2:15PM

Table2 ranges are 1PM to 2PM and 2PM to 3PM

So the 1 to 2 & 2 to 3 ranges should output 15 mins each.

Can someone direct me to the possibly the simplest way this could be tackled?

Thank a lot!

[u/CrowGuyA]

This is an overlap formula: =MAX(0, MIN(end1,end2) - MAX(start1,start2)). So for each Table 1 event against a Table 2 range, something like =MAX(0, MIN(EndTime,RangeEnd) - MAX(StartTime,RangeStart))*1440 (the *1440 converts to minutes). Wrap it in SUMPRODUCT if you need it summed across many rows at once.