# Maximum Overlapping Times

#### pencekj

##### New Member
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 Date Start Time End 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
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

#### Micron

##### Well-known Member
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.

#### FlameRetired

##### Active Member
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:

Replies
0
Views
99
Replies
13
Views
263
Replies
7
Views
125
Replies
4
Views
75
Replies
1
Views
92

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.

### Which adblocker are you using?

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

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