Maximum Overlapping Times

pencekj

New Member
Joined
Jul 16, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I have a dataset of call data. Date is 1 column, start time for a phone call is in 1 column, and end time is in another column. This is for a large team of people so a lot of the calls overlap. How could I find what the maximum number of calls overlapping at once is? This is thousands and thousands of calls but I've copied some of the sample data below.


Call DateStart TimeEnd Time
4/19/2022​
4:53:30 PM​
4:57:08 PM​
4/19/2022​
4:17:51 PM​
4:25:09 PM​
4/19/2022​
4:02:11 PM​
4:15:56 PM​
4/19/2022​
2:34:49 PM​
2:43:27 PM​
4/19/2022​
1:11:25 PM​
1:15:41 PM​
4/19/2022​
1:04:08 PM​
1:09:20 PM​
4/19/2022​
1:01:48 PM​
1:03:31 PM​
4/19/2022​
12:53:13 PM​
1:04:30 PM​
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

pencekj

New Member
Joined
Jul 16, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I tried this but it doesn't seem to work

=COUNTIFS(J:J,J2,L:L,"<="&K2,K:K,">="&L2)

J=call date
K=start time
L=end time
 
Upvote 0

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
Did something like this for overlapping machinery downtime.
Suggest you:
- put the data rows in chronological order by one time or the other (let's say Start Time)
- formula in Column D checks IF C1 falls between B2 and C2 and if so, returns 1 (or any value you want)
- drag that formula down the range
- range E2 could contain the Count formula for D as far down as you want to go (or sum if using 1's).
That would give you the total number of times there was overlap, but not for any specific period.

I doubt I would easily be able to find this old file, so hope that helps get you started. In my case, it was a bit more complicated because as one unit went down, the count increased. When one went back into service the count had to decrease. You don't seem to need that.
 
Upvote 0

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
340
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
After looking at other's posts I'm not sure I have the concept. I'll post just the same, but not the whole range here. Converting the time ranges to seconds created an output range of D4:K828. This only show down to row 25 which includes a summary check and Conditional Formatting confirmations.

Since I have not contributed here for a long time so I am not familiar with how to edit posts. Since this may be a lame presentation that is not self explanatory I don't know what to anticipate. So if you have questions about this I will likely have to answer in another post.

Just found how to edit. Still not how self explanatory this will be.

Book1
ABCDEFGHIJKLMNO
1Call DateStart TimeEnd TimeFromMr Excelpencekj
24/19/20224:53:30 PM4:57:08 PM4:53:30 PM4:17:51 PM4:02:11 PM2:34:49 PM1:11:25 PM1:04:08 PM1:01:48 PM12:53:13 PM
34/19/20224:17:51 PM4:25:09 PM4:57:08 PM4:25:09 PM4:15:56 PM2:43:27 PM1:15:41 PM1:09:20 PM1:03:31 PM1:04:30 PM44
44/19/20224:02:11 PM4:15:56 PM60810586715773152489474854704846908463934704821:04:08 PM
54/19/20222:34:49 PM2:43:27 PM60811586725773252490474864704946909463944704921:04:09 PM
64/19/20221:11:25 PM1:15:41 PM60812586735773352491474874705046910463954705021:04:10 PM
74/19/20221:04:08 PM1:09:20 PM60813586745773452492474884705146911463964705121:04:11 PM
84/19/20221:01:48 PM1:03:31 PM60814586755773552493474894705246912463974705221:04:12 PM
94/19/202212:53:13 PM1:04:30 PM60815586765773652494474904705346913463984705321:04:13 PM
1060816586775773752495474914705446914463994705421:04:14 PM
1160817586785773852496474924705546915464004705521:04:15 PM
1260818586795773952497474934705646916464014705621:04:16 PM
1360819586805774052498474944705746917464024705721:04:17 PM
1460820586815774152499474954705846918464034705821:04:18 PM
1560821586825774252500474964705946919464044705921:04:19 PM
1660822586835774352501474974706046920464054706021:04:20 PM
1760823586845774452502474984706146921464064706121:04:21 PM
1860824586855774552503474994706246922464074706221:04:22 PM
1960825586865774652504475004706346923464084706321:04:23 PM
2060826586875774752505475014706446924464094706421:04:24 PM
2160827586885774852506475024706546925464104706521:04:25 PM
2260828586895774952507475034706646926464114706621:04:26 PM
2360829586905775052508475044706746927464124706721:04:27 PM
2460830586915775152509475054706846928464134706821:04:28 PM
2560831586925775252510475064706946929464144706921:04:29 PM
Sheet1
Cell Formulas
RangeFormula
N3N3=SUM(--ISNUMBER(MATCH(All_Seconds,M4#,0)))
D4:K4D4=SEQUENCE(MOD(D$3-D$2,1)*86400,,D$2*86400,)
M4M4=MODE.MULT($D$4:$K$828)
N4:N25N4=COUNTIF(All_Seconds,M4)
O4:O25O4=M4/86400
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:K828Expression=--ISNUMBER(MATCH(D4,$M$4#,0))textNO
 
Last edited:
Upvote 0

Forum statistics

Threads
1,186,330
Messages
5,957,260
Members
438,296
Latest member
AoS80

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top