Tracking Date and Time overlaps

Hello. I'm trying to figure out a formula that would track a student's class schedule to see if there is a date/time conflict. I'm uploading an example of a ficticious student named Amanda for you to review.

According to the example, I should be prompted that there's a conflict between Amanda's first two classes because they both meet on Monday (M) and Wednesday (W) and the time during each of those classes overlaps.

This will need to be checked per student, based on their ID in column A.

Any help you could give would be greatly appreciated!

Thanks!

The following macro works on the sheet provided. You may need an extension to it if your sheet has multiple students on it.

Please Login or Register  to view this content.

Thank you for your help. This looks impressive but I've never used a macro. How do I use one to get this to work?

Hit Alt F11 to open the VBA editor

Insert - Module

Paste the macro into the empty sheet

Hit Alt F11 again to get back to Excel

Tools - macro - Check.

If this looks to be doing the right sort of thing, maybe you could reply with a sheet showing what it would look like for multiple students so that any amendments can be made.

Thanks mrice. This works wonderfully for the example I gave. In real life, however, there will be several rows in the table . For example, student A might have four classes (one per row) and each of his/her classes would need to be checked for an overlap. Once student A has been dealt with, the system should then do the same thing for student B by checking each of his rows and showing which ones overlap. Etc. on down the spreadsheet until each student has been checked for overlaps.

I'll attach another file showing at least two students, but understand that in reality there will be several hundred rows with each student having approximately 4-5, based on the number of classes they are taking.

Thanks again in advance!

Try this

Please Login or Register  to view this content.

I've made no assumptions that the rows will be sorted with ID's grouped together. If this is the case and the macro runs slowly then some improvements could be made to reduce the number of comparisons that are made.

Thanks Martin for your help. I used the last code you sent and everything worked perfectly. Only one thing that I noticed that got by when the code ran was classes that have days such as MW were not checked against classes that had days such as MWF. For example, Bobby Davis in my uploaded list... Bobby has an overlap on Monday (M) and Wednesday (W) with his classes.

If everyone were comparing MWF to MWF or TR to TR (TR = Thursday) then overlaps are caught. The problem is where there's a MWF compared to a MW that it's not catching the overlaps. Hope this helps.

Before coding, how would Tuesday's be represented i.e. would every day be MTUWTRF? Do we need to worry about weekends?

What are the time limits on the day? - I've assumed 24 hours and this might be leading to a lot of redundant processing?

Time limits could vary. Most classes would take place anywhere from 8:00 a.m. through 10:00 p.m. As for the dates, we only need to worry about Monday-Friday.

M - Monday
T - Tuesday
W - Wednesday
TR - Thursday
F - Friday

Classes can be any combination of the days listed above.

Ok, try this.

Please Login or Register  to view this content.

I've taken the opportunity to make the code run a bit faster by eliminating comparisons and assuming that rows will be grouped by ID.

Martin, this worked perfectly. It even found some people with conflicts that we had missed while checking each line ourselves. Thank you so much!

Jeff

I know this looks like an old thread, but I'm excited to find it. I'm trying to do something similar where I'm checking for time overlaps on employees. I was thinking about an excel file for each employee with a sheet for each person they support. So, for instance Employee A may support Johnny, Jimmy and Susie, so she would have three sheets in her Excel file. Some days she supports all three for 3 hours each, other times it may vary. It seems I could use something similar to check for overlaps in time and dates, but can I do it across sheets? I'm NOT a VB expert, not even a novice. Can you help with that? If so, thank you in advance!