Overlapping count times checking

[quinnvu24]

Hi,

Below is the data. Is there any formula that define the specific hour or specific period that overlap 3 times. Overlap 2 times is fine.
I tried to separate the date and time then try Sum product but not work.
I also tried to design the hour for each date it was used however unsure how to design and display data.

Example is below 18/03/2024, 08:00PM - 09:00PM is overlapped 3 times.

Thanks all!

 

[Eric W]

Welcome to the MrExcel forum!

I assumed that column A is the start of the period, and column B is the end of the period. If so, we can count how many intervals overlap with the following formula:

Book1
	A	B	C	D
1	18/03/2024 03:00 PM	18/03/2024 08:00 PM		3
2	18/03/2024 03:00 PM	18/03/2024 08:00 PM		3
3	18/03/2024 06:00 AM	18/03/2024 10:00 PM		6
4	18/03/2024 06:00 AM	18/03/2024 10:00 PM		6
5	18/03/2024 08:00 PM	18/03/2024 10:00 PM		4
6	18/03/2024 08:00 PM	18/03/2024 09:00 PM		4
7	18/03/2024 08:00 PM	19/03/2024 12:00 AM		5
8	18/03/2024 10:00 PM	19/03/2024 06:00 AM		2
9	18/03/2024 12:00 AM	18/03/2024 06:00 AM		0
10	19/03/2024 03:00 PM	19/03/2024 08:00 PM		1
11	19/03/2024 03:00 PM	19/03/2024 08:00 PM		1
12	19/03/2024 06:00 AM	19/03/2024 10:00 AM		1
13	19/03/2024 06:00 AM	19/03/2024 10:00 AM		1
14	19/03/2024 08:00 PM	19/03/2024 10:00 AM		-1
15	19/03/2024 08:00 PM	20/03/2024 12:00 AM		1
16	19/03/2024 10:00 PM	20/03/2024 06:00 AM		1
17	19/03/2024 12:00 AM	19/03/2024 06:00 AM		1
Sheet3

Cell Formulas
Range	Formula
D1:D17	D1	=SUM(--(IF(B1<B$1:B$17,B1,B$1:B$17)-IF(A1>A$1:A$17,A1,A$1:A$17)>0))-1

Note that the formula has -1 on the end, this is to subtract the case where the interval overlaps itself. Your example that row 6 overlaps 3 other ranges seems to be off, I counted 4 ranges, specifically rows 3, 4, 5, and 7. Also notice row 14 has a value of -1 which makes no sense. This is because the start time is after the end time. Is there a mistake in the table, or in my recreation of it?

Anyway, you can use the formula on the sheet if you want, or convert it to a Conditional Formatting formula to highlight the desired rows.