Figure out occurence of overlapping date & time

OK,

this may seem harder than it is.

Assume Start_Time is Column_A and End_Time is Column_B

In another column, for example column C, enter this formula in row 2 (I assume row 1 has titles) and copy down your 30,000 lines:

=SUMPRODUCT(--($A$2:$A$30000<A2),--($B$2:$B$30000>A2))+1

This formula in plain English says: I am the call that is recorded in cell A2. Show me how many other calls in the list have a start time smaller than me AND an end time greater than my start time. Because the SUMPRODUCT formula only counts the OTHER calls, add a 1 for the call recorded in this row.

The result is a number. For each call you can see how many calls occurred in the same time window as this call.

Now, somewhere else on your spreadsheet, you could count how often you have 24 concurrent calls with a formula like

=COUNTIF(C2:C30000,24)

Does that work for you?

Hi, glad it worked for you.

the $A$2 (absolute reference) in a formula will always stay $A$2, even when the formula is copied across or down. The A2 (relative reference) will change to A3, A4, A5 etc. when copied down. In this formula I use the absolute reference for the range I want to check. This always needs to be A2 through to A30000, so I have to use absolute referencing when I copy the formula down. But in each row, I want to compare only the value in column A in the current row, so I use A2 in row 2, and when the formula is copied down, the relative reference will be adjusted to the current row.

Now about the --
A statement like $A$2:$A$30000<A2 returns either TRUE or FALSE. If I put the -- before the statement, it will translate TRUE into 1 and FALSE into 0, so I can actually use it for calculations. The SUMPRODUCT will multiply the results of each argument.

In a formula with 7 instead of 30000 lines the part $A$2:$A$8<A2 may return {TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE}, which with the -- is then translated into {1, 1, 1, 0, 0, 0, 0}.

the next argument $B$2:$B$8>A2 may return {FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE}, which with the -- is translated into {0, 1, 1, 1, 0, 0, 0}

These two arrays will be multiplied
{1, 1, 1, 0, 0, 0, 0} *
{0, 1, 1, 1, 0, 0, 0}

the first element of the first array is multiplied with the first element of the second array, the second element of the first array is multiplied with the second element ofthe second array, etc, and the result is

{0, 1, 1, 0, 0, 0, 0}

Sumproduct then adds up these results to return 2

SUMPRODUCT is a very powerful formula, but it can take a while to internalise how it works.

hth

Hi teylyn,

That's awesome. I was trying to figure out how to compare the value without the formulas changing. The absolute reference is perfect. Thanks for that tip.
As for the array, basically you would have to have both the results be TRUE for the end result to be TRUE. That makes sense.
So the SUMPRODUCT command repeats this calculation for however many results gets returned from the range of numbers chosen?

OK, so time to kind of fine tune this calculation. My original test was insufficient to check for all the different ways two time lines might overlap.
A1=call 1 start, B1=call 1 end
A2=call 2 start, B2=call 2 end

Overlap 1: A1 is between A2 and B2 (call 1 starts during call 2's interval)
Overlap 2: B1 is between A2 and B2 (call 1 ends during call 2's interval)
Overlap 3: A1 < A2 AND B1 > B2 (call 1 starts before and ends after call 2; basically this interval eclipses the other one)
Overlap 4: A1 > A2 AND B1 < B2 (call 1 starts and ends while call 2 is ongoing; basically this interval is inside the other one)

Test for Overlap 1
=SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>A1))
Test for Overlap 2
=SUMPRODUCT(--($A$1:$A$30000<B1),--($B$1:$B$30000>B1))
Test for Overlap 3
=SUMPRODUCT(--($A$1:$A$30000>A1),--($B$1:$B$30000<B1))
Test for Overlap 4
=SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>B1))

However, I unable to find a combination of formulas to test to see if ANY of the Overlap tests were TRUE

I was thinking something like this but it doesn't work. It takes the results of the OR as a single computation. I can't get it to repeat the way SUMPRODUCT goes through the entire range.

=OR(SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>A1)),SUMPRODUCT(--($A$1:$A$30000<B1),--($B$1:$B$30000>B1)),SUMPRODUCT(--($A$1:$A$30000>A1),--($B$1:$B$30000<B1)),SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>B1)))+1

I thought about calculating each Overlap test separately but that won't work either because I'll be double/triple counting some numbers. That's because a scenario like Overlap 4 will match both Overlap 1 and 2 also. I just need it to register a single time if ANY of the tests are TRUE.

Any ideas?
Thanks a lot.

Hi,
don't have much time today, so just a thought, not tested:

how about putting the four overlap sumproduct formulas in separate columns and use a fifth column to calculate the totals?

Hi Yonkers,

I think I've got it. I thought my head was going to explode, but it is really not that complicated.

You need three checks only:

1. Calls that start during the current call
2. Calls that end during the current call
3. Calls that start before and end after the current call.

The fourth check is not necessary, because it will be covered by 1 and 2.

Attached is a spreadsheet that lists 10 calls. I have created named ranges for the start and end date column, so it's easier to read and apply in formulas. A graph shows the start and duration of the calls, so it's easy to check if the formulas give the correct result.

Now, in col G I calculate check 1: calls that start during my interval (including me)
In col H I calculate check 2: calls that end during my interval (including me)

These two checks form the bulk of the concurrent calls, but I really only need the biggest of these two calculations.

On top of that I need to add calls that started before my interval and ended after my interval. This is calculated in column I

In column J I put it all together manually and in column K it is all in one formula. You really only need column K, the rest is just to illustrate the elements of the formula.

Try it and play around with the start and end times. The duration column is needed for the graph, so don't delete it.

cheers

Wow. That's awesome teylyn. You're absolutely right about the 3 checks. Not sure why I totally overlooked that.
I really appreciate you taking the time to create the spreadsheet. Everything was very clear and I totally understand how it's supposed to work.
Weird thing is though, I put in the formula on my sheet and it worked for the first row. However, when I copied it down to the rows below, I got a #VALUE error. Haven't been able to troubleshoot it too much but so far it looks exactly the same so I have no idea why it's failing. I'll try replacing the name with the actual absolute value ranges.
Thanks for your help and I'll let you know if I get it working.

hi yonkers, it's 8:30 pm on a Friday nite in NZ and I'm writing this on a crappy PDA lying in bed, so plz xcuse any typos or weird characters.

I'm glad you find my formula useful. don't know why it throws the #value error, though. if I were to do it, I'd create range names for the start and end time columns and use these in the formula. If you're using daily logs where the number of cells in a column changes all the time, look into dynamic range names that are defined with a combination of OFFSET(9) and COUNTA() to always include all populated cells in a column.

If you use range names you'll greatly reduce the risk of errors in the ranges used of the formula.

I'm keen to know how you're doing. This has been an intriguing challenge and I'd like to know if you managed to solve it to meet your requirements.

So, plz keep me updated and let me know if I can be of any more help. I'll be offline until Monday morning NZ time, though. My family needs me too.
cheers & have a gr8 weekend

teylyn

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

Hi,

in your original post you wrote

My phone system can handle 24 concurrent calls. I'm trying to see how many times throughout the month did we have 24 or more calls going on at once.

So, as I said above, althoug one single long call may during its duration overlap with 24 other calls, it may still be that these 24 calls are not active at the same time. You really want to see if 24 calls went on at the same time, which is not the same as overlapping with the current call.

See the difference?

Well, it seems like you've done it again. Your theory seems to bear it out. I ran that simple formula through all 30k calls and it looks like it gives me what I need.
What I couldn't wrap my head around was why I didn't need to check if a call overlapped with other calls beyond the initial start. Meaning... a call might only overlap with 2 calls when it first starts but because it lasts 2 hours it ends up overlapping with more calls.
But then I realized it didn't really matter. I just need to check when the initial call attempt is made. Because if I don't show that specific call as overlapping then the calls below it will capture the fact that was still ongoing, 2 hours later.
I'm still working the logic through my head but it's beginning to make sense. And it ends up being so simple. Just like we suspected it would be.
Thanks so very much.

Edit: I just went back and read through your post and you basically said the same thing I just did. lol. Well, I never said I wasn't dense. I had to go through the logic myself before I could truly understand it.

Nicoleemily,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread