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.

 

[agavin]

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.

Thank you, I am extremely new and appreciate the feedback.

 

[agavin]

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.

This is spectacular. I love the graph. I'm wondering if I can replicate it for data over a month. When I try, the series for incident closed overlaps with the duration, so I can't get the formatting correct where only the duration is colored.
It looks like there are no formulas to get the calculation; it's all in a macro. Is there a way to pull the macro into a formula or the pertinent parts into a formula?
I need to pull the formula into the live document.

 

[vletm]

agavin​

I've used only Your given data.
You should send more data for testing.
I've used Macro, maybe someone would do something same with formulas.
Why You ... need ... to pull it somewhere?

 

[agavin]

agavin​

I've used only Your given data.
You should send more data for testing.
I've used Macro, maybe someone would do something same with formulas.
Why You ... need ... to pull it somewhere?

The file I'm using is more complex, and I don't understand the macro well enough to apply it to the 'real' file. I've attached a better version of the file (I can't attach the real file); maybe you can help me figure it out.

 

[p45cal]

In the attached:
1. Your source Table1 minus its calculated columns.
2. A new results table at cell K1 which is the same as your table plus 4 columns:

• Call duration
• Count - the count of other incident numbers overlapping this one
• total overlap - the sum of all overlaps with other incident numbers
• incident Nos - a list of the other incident numbers it overlaps with

3. A pivot table at cell X1 showing overlap time of each unit.

4. A chart (below the new reults table).
If you filter the results table on column T (Count) to show only numbers (eliminate blanks), the chart will update to show only those incident numbers which have an overlap with another incident number (within the same Unit).

What you have to do when you want to update/change the source data in Table1:
Refresh both the result table at cell K1 and the pivot table at cell X1, in that order by right-clicking somewhere in each table and choosing Refresh, or by clicking the Refresh All button in the Queries & Connections section of the Data tab of the ribbon twice (once to update the Power Query table at cell K1 and again to update the pivot (which is based on that table):

 

[vletm]

... complex ... why?
Still I gotta guess - what do You really need?
... except those overlapping times.
I tested to show - number of units 'working' in same time - lower chart.
... Is there more than two units?
For You, it would be smoother to send a realistic file as possible that I could 'do-my-variation there'
... and later, You could add Your real data there.

 

[agavin]

... complex ... why?
Still I gotta guess - what do You really need?
... except those overlapping times.
I tested to show - number of units 'working' in same time - lower chart.
... Is there more than two units?
For You, it would be smoother to send a realistic file as possible that I could 'do-my-variation there'
... and later, You could add Your real data there.

The need is to report information our board of directors has requested. I'm not a programmer/developer, but I am the individual who has the most experience with Excel and analyzing data, so I'm tasked with reporting for the previous month and a cumulative total for the year on:

The number (#) of agency total incidents. (done)
The # of incidents where a second or third incident happened before the first incident was closed (Simultaneous incidents), (done)
The percent (%) of Simultaneous incidents. (done)
The # of incidents by department/unit. (done)
The # of simultaneous incidents by department/unit. (done)
The % of simultaneous incidents by department/unit. (done)
The number of incidents where the address is "PPRH". (done)
The number of instances where all three ambulances responded to an incident at the same time (Each department has more than one vehicle, but only one vehicle responds to each incident, so I group them into their corresponding department; if there are three incidents where an ambulance is responding that all overlap, then there were no ambulances available for a fourth incident.) (done)
The number of incidents where all three ambulances responded to an incident at the same time where at least one of them responded to "PPRH.". (done)

The new request is to present information specifically on simultaneous calls. The board wants to know how much time in a month no ambulances were available for an incident and how often this happens. I want to identify outliers in this information for the presentation, such as one call with significant overlap time vs many short overlapping calls. They also asked for a graph depicting when simultaneous calls occur and how much of the calls overlap. The graph you created does that part perfectly!

The number of vehicles for the ambulance is 3.
The number of vehicles for the PACT is variable depending on the day.

 

[vletm]

The board wants to know how much time in a month no ambulances were available for an incident and how often this happens.
... means ... How long time there has been those two moments as in below chart.

How should how often this happens should report?

Could You send whole December data?

Those You (done) seems to report in own sheet or how?

 

[agavin]

In the attached:
1. Your source Table1 minus its calculated columns.
2. A new results table at cell K1 which is the same as your table plus 4 columns:

• Call duration
• Count - the count of other incident numbers overlapping this one
• total overlap - the sum of all overlaps with other incident numbers
• incident Nos - a list of the other incident numbers it overlaps with

3. A pivot table at cell X1 showing overlap time of each unit.

4. A chart (below the new reults table).
If you filter the results table on column T (Count) to show only numbers (eliminate blanks), the chart will update to show only those incident numbers which have an overlap with another incident number (within the same Unit).

What you have to do when you want to update/change the source data in Table1:
Refresh both the result table at cell K1 and the pivot table at cell X1, in that order by right-clicking somewhere in each table and choosing Refresh, or by clicking the Refresh All button in the Queries & Connections section of the Data tab of the ribbon twice (once to update the Power Query table at cell K1 and again to update the pivot (which is based on that table):

View attachment 89823

Thanks, this is helpful, and I appreciate that I can copy the workflow from the queries and connections to replicate it.

 

[agavin]

The board wants to know how much time in a month no ambulances were available for an incident and how often this happens.
... means ... How long time there has been those two moments as in below chart.
View attachment 89831
How should how often this happens should report?

Could You send whole December data?

Those You (done) seems to report in own sheet or how?

I've attached the December data. I used the report for the January board meeting, and any personally identifiable information has been removed.

How often do all three ambulances respond to an incident at the same time - how to report - I need to report this in two ways:

1. Count of instances where this happened. (ex. using made-up numbers: 3 times in December)
2. The total time that three ambulances were responding to incidents simultaneously. (ex. using made-up numbers: the first instance of all three ambulances responding at the same time was 20 minutes (where incident B overlapped incidents A and C by a total of 20 minutes), the second instance was 2 minutes, the third instance was 2 hours for a total of 2:22)

Other than the graph, I only need to report the numbers, they will not see the dataset. Maybe this is obvious, but maybe not.

 

[agavin]

The board wants to know how much time in a month no ambulances were available for an incident and how often this happens.
... means ... How long time there has been those two moments as in below chart.
View attachment 89831
How should how often this happens should report?

Could You send whole December data?

Those You (done) seems to report in own sheet or how?

The time overlapping, total overlap, and Overlaps columns (as well as the graph) from the first solution you proposed are excellent! I'd love to have it do something like that. I just don't know how to use a formula or data query to make it do that...