• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

finding overlapping time in data

anich

New Member
I have a report from agency phones and need to find how many lines are in use at the same time. I can find the start and end times, but I cannot figure out how to have a graph/formula assist me in finding the data i need.
 

Attachments

anich

New Member
Thank you, That looks amazing. How did you do that?
Is there a way for me to learn how to do that for any future requests at work? This type of data is not what we normally do, as we are a human service agency.
 

vletm

Excel Ninja
anich
You wrote: This type of data is not what we normally do, as we are a human service agency.
Why did You sent data, which You do not normally use and
after that You would like to know how to solve data which You don't normally use... hmm?
Okay... basic steps

#1 solve each rows end time
#2 solve how many times each rows start & end times overlaps with data
#3 clear each rows end time ( no need more )
#4 create XYchart with start time and duration & overlaps as in a sample
#5 create two part of codes for date-selection handling - one (yellow) filters data with given date and other enables steps forward and backward
 

anich

New Member
We are trying to figure out how many phone trunks we actually need to have and our phone company is not being helpful with the process.
I have access to the phone admin panel, so I was chosen to look at the data and am by no means an Excel Wizard or Ninja :)

Thank you so much for your help. It is greatly appreciated
 

Peter Bartholomew

Well-Known Member
Shame, I have the start of a dynamic array solution and no one to share it with :(.

Since the call start datetimes are sorted (in reverse), all that is needed to determine the count of calls started from the beginning of recorded time is a simple sequence function
= SEQUENCE(n,1,n,-1)
Finding the number of 'calls ended' for each timestep is a little more tricky. SUMIFS will do it but searching over 6000 records 6000 times starts to get computationally expensive with the computer hanging for a few seconds before returning an answer. What I hit upon as an alternative was to calculate the 'end datetimes' using
= StartDatetime + Duration
and then sort using the function
= SORT(EndDatetime#)
[# returns the dynamic range rather than the content of the anchor cell].
The SORT appears to be an exceptionally fast operation with no perceptible delay.

The 'count of calls ended' at each 'call start' then only needs a rapid bisection search to give the row index
= IFERROR( MATCH(StartDatetime, EndSorted), 0 )
Finally,
= StartedCount - EndedCount
gives the count of calls in progress immediately after a new call is started.

Note: This requires Office 365 insider (or perhaps 'monthly') to have the required functionality.
 

Attachments

Top