Teacher Class overlap

[webdevptg]

Hi,


I have a huge listing with about 15000 records of all classes taught by teachers in a school.

Sometimes teachers make a mistake when registering the classes, on the date, or on the time or even the duration, they just don't make a mistake about the teacher's name because it already appears by default

I need to write a formula to show me if there is overlapping or not. In the example below, teacher John Doe is over-allocated on April 16th between 4:00 PM and 5:00 PM, because he has a class starting at 2:00 PM with duration of 3 hours, and have other class starting at 4:00 PM with duration of 2 hours.

In the case of Anne Ericson, she was really lacking in attention, she registered the session 2 times, in the same day, at the same time and duration.

In this way, I want the result to be 1 when there is no over-allocation, in the example where John and Anne are totally or partially in 2 classes the result will be 2, but it can also be 3 or more....

I've already been dealing with countifs, but I'm having difficulty getting it to give me the results for the various scenarios...

Below I leave the image and link to see if you can help me.


Thanks


SAMPLE WORKBOOK

 

[Eric W]

You're in luck! I just answered a question just like this one. Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Date	Start Time	Duration	Teacher	Overlap
2	4/15/2021	9:00 AM	4	Jonh Doe	1
3	4/15/2021	2:00 PM	3	Jonh Doe	1
4	4/16/2021	11:00 AM	2	Jonh Doe	1
5	4/16/2021	2:00 PM	3	Jonh Doe	2
6	4/16/2021	9:00 AM	3	Anne Ford	1
7	4/16/2021	1:00 PM	4	Anne Ford	1
8	4/16/2021	4:00 PM	2	Jonh Doe	2
9	4/17/2021	9:00 AM	2	Anne Ford	1
10	4/17/2021	11:00 AM	2	Anne Ford	2
11	4/17/2021	11:00 AM	2	Anne Ford	2
12	4/19/2021	9:00 AM	4	Anne Ford	1
13	4/19/2021	9:00 AM	4	Jonh Doe	1
Sheet23

Cell Formulas
Range	Formula
E2:E13	E2	=SUM(--(IF($D$2:$D$13<>D2,0,IF($A$2:$A$13+$B$2:$B$13+$C$2:$C$13/24<A2+B2+C2/24,$A$2:$A$13+$B$2:$B$13+$C$2:$C$13/24,A2+B2+C2/24)-IF($A$2:$A$13+$B$2:$B$13>A2+B2,$A$2:$A$13+$B$2:$B$13,A2+B2))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 365, you won't need to use Control+Shift+Enter when entering the formula.

 

[Toadstool]

Hi Webdevptg,

Your Google Doc is private so I couldn't download. Does this do what you want?

Webdevptg2.xlsx
	A	B	C	D	E
1	Date	Start Time	Duration	Teacher	Overlap
2	4/15/2021	9:00	4	John	1
3	4/15/2021	14:00	3	John	1
4	4/16/2021	11:00	2	John	1
5	4/16/2021	14:00	3	John	2
6	4/16/2021	9:00	3	Anne	1
7	4/16/2021	13:00	4	Anne	1
8	4/16/2021	16:00	2	John	2
9	4/17/2021	9:00	2	Anne	1
10	4/17/2021	11:00	2	Anne	2
11	4/17/2021	11:00	2	Anne	2
12	4/19/2021	9:00	4	John	1
13	4/19/2021	9:00	4	Anne	1
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=SUMPRODUCT(($A$2:$A$20000=A2)*($D$2:$D$20000=D2)*((B2<$B$2:$B$20000+TIME($C$2:$C$20000,0,0))*(B2+TIME(C2,0,0)>$B$2:$B$20000)))

 

[webdevptg]

You're in luck! I just answered a question just like this one. Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Date	Start Time	Duration	Teacher	Overlap
2	4/15/2021	9:00 AM	4	Jonh Doe	1
3	4/15/2021	2:00 PM	3	Jonh Doe	1
4	4/16/2021	11:00 AM	2	Jonh Doe	1
5	4/16/2021	2:00 PM	3	Jonh Doe	2
6	4/16/2021	9:00 AM	3	Anne Ford	1
7	4/16/2021	1:00 PM	4	Anne Ford	1
8	4/16/2021	4:00 PM	2	Jonh Doe	2
9	4/17/2021	9:00 AM	2	Anne Ford	1
10	4/17/2021	11:00 AM	2	Anne Ford	2
11	4/17/2021	11:00 AM	2	Anne Ford	2
12	4/19/2021	9:00 AM	4	Anne Ford	1
13	4/19/2021	9:00 AM	4	Jonh Doe	1
Sheet23

Cell Formulas
Range	Formula
E2:E13	E2	=SUM(--(IF($D$2:$D$13<>D2,0,IF($A$2:$A$13+$B$2:$B$13+$C$2:$C$13/24<A2+B2+C2/24,$A$2:$A$13+$B$2:$B$13+$C$2:$C$13/24,A2+B2+C2/24)-IF($A$2:$A$13+$B$2:$B$13>A2+B2,$A$2:$A$13+$B$2:$B$13,A2+B2))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 365, you won't need to use Control+Shift+Enter when entering the formula.

Works fine, but the @Toadstool solution is more clean.
Thank you

 

[webdevptg]

Hi Webdevptg,

Your Google Doc is private so I couldn't download. Does this do what you want?

Webdevptg2.xlsx
	A	B	C	D	E
1	Date	Start Time	Duration	Teacher	Overlap
2	4/15/2021	9:00	4	John	1
3	4/15/2021	14:00	3	John	1
4	4/16/2021	11:00	2	John	1
5	4/16/2021	14:00	3	John	2
6	4/16/2021	9:00	3	Anne	1
7	4/16/2021	13:00	4	Anne	1
8	4/16/2021	16:00	2	John	2
9	4/17/2021	9:00	2	Anne	1
10	4/17/2021	11:00	2	Anne	2
11	4/17/2021	11:00	2	Anne	2
12	4/19/2021	9:00	4	John	1
13	4/19/2021	9:00	4	Anne	1
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=SUMPRODUCT(($A$2:$A$20000=A2)*($D$2:$D$20000=D2)*((B2<$B$2:$B$20000+TIME($C$2:$C$20000,0,0))*(B2+TIME(C2,0,0)>$B$2:$B$20000)))

Amazing, very clean...Now i look this solution to understand