Figure out occurence of overlapping date & time

**Yonkers** · 05-22-2009, 05:23 PM

Hi Telyn,

It was a typo on my part when I created the range names. I forgot a $ in front of one of the numbers for the absolute reference. It confused me because it worked for the first row and not any of the others. Now the formula works on all rows. So thank you very much for that because I learned a whole lot from just trying to understand that formula.
Now I'm trying to figure out how times I had 24 calls going on at once.
So the criteria for this formula seems to be.

1. Call_X overlaps with at least 24 calls and at least the 23 calls immediately after Call_X all overlap with at least 24 calls.

=COUNTIF(C2:C25>24)

And then copy that down? Seems like there should be something simple to calculate this, since the hard work has been done already. Still thinking this through but wanted to update you at least.
Thank you very much and have a great weekend.

**teylyn** · 05-25-2009, 06:49 PM

Hi Yonkers,

I've had another think about the challenge, and I believe we're attacking it from the wrong angle, because we're not asking the right questions.

Currently, we're asking: how many calls went on during the current call? Example: We have a 10 minute long call. During its duration there were 6 other short calls, none of the short calls overlapped:

long call ******************
short calls ** ** ** ** ** **

The formula I've been working on would count 7 calls total. But that's not quite right. At any one point in time there were only ever two calls going on at the same time.

Therefore, I think the question we need to ask is: When Call A starts, how many other calls are already going on?

We really only need to look at the start of the call, because if during Call A another Call B starts, that Call B will then count its respective concurrent calls, including Call A.

When you look at the chart I made in the previous post, the calls run horizontally and you want to know how often a vertical line crosses 24 calls. Draw that vertical line at the start of each call and you have your result.

So, very simple formula to calculate calls going on at the same time (using the range names for the start and end times of calls):

=SUMPRODUCT(--(B2>=callStarts),--(B2<=callEnds))

In the attached workbook I've also build a little table to show the counts of the concurrent calls.

Take some time to mull over that. It's not a simple challenge, but the solution actually is simple. I feel that looking at the chart helps to get a better feel for what needs to be analysed, and playing with the start and end times of calls will help visualise certain scenarios.

Hope that helps

teylyn

**Yonkers** · 05-26-2009, 05:49 PM

Hi Teylyn,

Sorry for the slow response. And thanks for mulling this over. I still haven't had a chance to digest all of what you wrote. I will do so tonight.
But I wanted to reply and say that that I don't think our previous efforts were for naught. Our previous effort was to see how many other calls the current call overlapped with. Well, the only calls I care about are ones that overlap with at least 23 others. None of the calls with less than 24 overlaps will fit the criteria that I care about. So through our effort from last week, I was able to rule out 26k of the 30k calls. So we only need to work with 4k phone calls now

Now I need some time looking at your most recent post.
Thanks again.

**JazzMak** · 03-04-2013, 10:25 PM

Thank you so very much for this solution! I used it to calculate the maximum number of desks being utilized concurrently in a very popular space in a healthcare facility. This worked wonderfully.

Thank you again. I had been struggling with pivot charts and vba for quite some time.