Maximum Overlapping Times

[pencekj]

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​

 

[pencekj]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Call Date	Start Time	End Time							From	Mr Excel	pencekj
2	4/19/2022	4:53:30 PM	4:57:08 PM	4:53:30 PM	4:17:51 PM	4:02:11 PM	2:34:49 PM	1:11:25 PM	1:04:08 PM	1:01:48 PM	12:53:13 PM
3	4/19/2022	4:17:51 PM	4:25:09 PM	4:57:08 PM	4:25:09 PM	4:15:56 PM	2:43:27 PM	1:15:41 PM	1:09:20 PM	1:03:31 PM	1:04:30 PM			44
4	4/19/2022	4:02:11 PM	4:15:56 PM	60810	58671	57731	52489	47485	47048	46908	46393		47048	2	1:04:08 PM
5	4/19/2022	2:34:49 PM	2:43:27 PM	60811	58672	57732	52490	47486	47049	46909	46394		47049	2	1:04:09 PM
6	4/19/2022	1:11:25 PM	1:15:41 PM	60812	58673	57733	52491	47487	47050	46910	46395		47050	2	1:04:10 PM
7	4/19/2022	1:04:08 PM	1:09:20 PM	60813	58674	57734	52492	47488	47051	46911	46396		47051	2	1:04:11 PM
8	4/19/2022	1:01:48 PM	1:03:31 PM	60814	58675	57735	52493	47489	47052	46912	46397		47052	2	1:04:12 PM
9	4/19/2022	12:53:13 PM	1:04:30 PM	60815	58676	57736	52494	47490	47053	46913	46398		47053	2	1:04:13 PM
10				60816	58677	57737	52495	47491	47054	46914	46399		47054	2	1:04:14 PM
11				60817	58678	57738	52496	47492	47055	46915	46400		47055	2	1:04:15 PM
12				60818	58679	57739	52497	47493	47056	46916	46401		47056	2	1:04:16 PM
13				60819	58680	57740	52498	47494	47057	46917	46402		47057	2	1:04:17 PM
14				60820	58681	57741	52499	47495	47058	46918	46403		47058	2	1:04:18 PM
15				60821	58682	57742	52500	47496	47059	46919	46404		47059	2	1:04:19 PM
16				60822	58683	57743	52501	47497	47060	46920	46405		47060	2	1:04:20 PM
17				60823	58684	57744	52502	47498	47061	46921	46406		47061	2	1:04:21 PM
18				60824	58685	57745	52503	47499	47062	46922	46407		47062	2	1:04:22 PM
19				60825	58686	57746	52504	47500	47063	46923	46408		47063	2	1:04:23 PM
20				60826	58687	57747	52505	47501	47064	46924	46409		47064	2	1:04:24 PM
21				60827	58688	57748	52506	47502	47065	46925	46410		47065	2	1:04:25 PM
22				60828	58689	57749	52507	47503	47066	46926	46411		47066	2	1:04:26 PM
23				60829	58690	57750	52508	47504	47067	46927	46412		47067	2	1:04:27 PM
24				60830	58691	57751	52509	47505	47068	46928	46413		47068	2	1:04:28 PM
25				60831	58692	57752	52510	47506	47069	46929	46414		47069	2	1:04:29 PM
Sheet1

Cell Formulas
Range	Formula
N3	N3	=SUM(--ISNUMBER(MATCH(All_Seconds,M4#,0)))
D4:K4	D4	=SEQUENCE(MOD(D$3-D$2,1)*86400,,D$2*86400,)
M4	M4	=MODE.MULT($D$4:$K$828)
N4:N25	N4	=COUNTIF(All_Seconds,M4)
O4:O25	O4	=M4/86400

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:K828	Expression	=--ISNUMBER(MATCH(D4,$M$4#,0))	text	NO