Calculate the amount of overlapping time

[agavin]

LINK to Google Doc

I am trying to calculate several things for an ambulance company based on the time a unit is dispatched and the time the incident is closed.
1. How long the call took.
2. Is there more than one call at any given time/simultaneous calls?
3. How long was a unit responding to two or more calls simultaneously?

I have formulas for determining the call time and whether a simultaneous call is occurring, but I cannot figure out how long the overlapping calls took with a formula. I manually calculated the overlapping calls in the Google Doc, but I would love a formula to drag down to the live document. Any help is appreciated!

 

[vletm]

Moderator Note:
Is that file Excel-file as it should be with Excel-forum?
It's always smoother and safer for others to send a file here than ... link to somewhere.

 

[p45cal]

On Sheet3 of the attached workbook, a Power Query solution. It uses only the 3 columns of data in columna A,B & C (calculating the duration for itself).
The result table at cell I1 has the column Overlap which is the sum of all overlaps for that row with all table's rows for that Unit.
The result table at cell P1 is the summary. I've divided the sum of the overlaps by 2 as per your "return overlap_sum / 2 # Divide by 2 to avoid double-counting overlaps", but I'm not 100% sure that's correct, so over to you to check.
Being Power Query, if you change the source data, the tables need refreshing as you would a pivot table.

 

[vletm]

Here other rough sample based above files data,
which could show something You've asked.
Press [ Do It ]-button to refresh results.
Note: A chart shows only those 15 rows data.