Teacher Class overlap

webdevptg

Board Regular
Joined
May 2, 2019
Messages
51
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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
 

Attachments

  • Capturar.JPG
    Capturar.JPG
    121.8 KB · Views: 14

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You're in luck! I just answered a question just like this one. Try:

Book1 (version 1).xlsb
ABCDE
1DateStart TimeDurationTeacherOverlap
24/15/20219:00 AM4Jonh Doe1
34/15/20212:00 PM3Jonh Doe1
44/16/202111:00 AM2Jonh Doe1
54/16/20212:00 PM3Jonh Doe2
64/16/20219:00 AM3Anne Ford1
74/16/20211:00 PM4Anne Ford1
84/16/20214:00 PM2Jonh Doe2
94/17/20219:00 AM2Anne Ford1
104/17/202111:00 AM2Anne Ford2
114/17/202111:00 AM2Anne Ford2
124/19/20219:00 AM4Anne Ford1
134/19/20219:00 AM4Jonh Doe1
Sheet23
Cell Formulas
RangeFormula
E2:E13E2=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.
 
Upvote 0
Hi Webdevptg,

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

Webdevptg2.xlsx
ABCDE
1DateStart TimeDurationTeacherOverlap
24/15/20219:004John1
34/15/202114:003John1
44/16/202111:002John1
54/16/202114:003John2
64/16/20219:003Anne1
74/16/202113:004Anne1
84/16/202116:002John2
94/17/20219:002Anne1
104/17/202111:002Anne2
114/17/202111:002Anne2
124/19/20219:004John1
134/19/20219:004Anne1
Sheet1
Cell Formulas
RangeFormula
E2:E13E2=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)))
 
Upvote 0
You're in luck! I just answered a question just like this one. Try:

Book1 (version 1).xlsb
ABCDE
1DateStart TimeDurationTeacherOverlap
24/15/20219:00 AM4Jonh Doe1
34/15/20212:00 PM3Jonh Doe1
44/16/202111:00 AM2Jonh Doe1
54/16/20212:00 PM3Jonh Doe2
64/16/20219:00 AM3Anne Ford1
74/16/20211:00 PM4Anne Ford1
84/16/20214:00 PM2Jonh Doe2
94/17/20219:00 AM2Anne Ford1
104/17/202111:00 AM2Anne Ford2
114/17/202111:00 AM2Anne Ford2
124/19/20219:00 AM4Anne Ford1
134/19/20219:00 AM4Jonh Doe1
Sheet23
Cell Formulas
RangeFormula
E2:E13E2=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
 
Upvote 0
Hi Webdevptg,

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

Webdevptg2.xlsx
ABCDE
1DateStart TimeDurationTeacherOverlap
24/15/20219:004John1
34/15/202114:003John1
44/16/202111:002John1
54/16/202114:003John2
64/16/20219:003Anne1
74/16/202113:004Anne1
84/16/202116:002John2
94/17/20219:002Anne1
104/17/202111:002Anne2
114/17/202111:002Anne2
124/19/20219:004John1
134/19/20219:004Anne1
Sheet1
Cell Formulas
RangeFormula
E2:E13E2=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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top