Tracking schedule confllicts

spudulene

New member
Joined
Aug 20, 2019
Messages
20
Reaction score
0
Points
1
Excel Version(s)
Excel 2019
I apologize, but I am new here and my thread has not appeared yet. I am sorry if I am sending this multiple times.
I am a professor and I want to correlate multiple student schedules to see when there are the fewest number of conflicts.
Count is important. Below on the right are the data that would be used to populate the table on the left.
I have created the table on the left manually, so one can see what it should look like after using the formula.
For example, there are two times on the right that cover T(uesday) from 8:30 - 8:45. The full spreadsheet would go from 8:00 am - 5:00 pm and have a couple hundred days, start and end times.
I would consider myself and intermediate excel user. I tried multiple attempts using countifs statements, but could not get past the first row of input data. Any help would be appreciated.


TimeDayDaysStartEnd
MTWRFTR8:009:00
8:0001010W9:209:45
8:1501010MWF9:009:50
8:3002010T8:308:45
8:4502111WF8:459:45
9:0011112
9:1511212
9:3011212
9:4511011
 
Hi and welcome
please post a sample sheet.Click " Go advanced - Manage attachments" and follow the wizard. Thanks
 
Here is an excel file with the data above.
Thank you.
 

Attachments

  • scheduleconflict.xlsx
    9.2 KB · Views: 12
Does the attached give the right answers?
 

Attachments

  • ExcelGuru10178scheduleconflict.xlsx
    11.6 KB · Views: 16
Wow! That is awesome. The answers were correct (and the one I hand generated was not, :hurt:.) I guess I can make that as long as I want by expanding the times on the left, changing the fixed range for the three columns on the right in formula in cell B3 (first cell with formula) and copying that to the rest of the table. I will play with it. Thank you very much.
 
My conflict table works brilliantly, but there are a couple of bugs that I cannot rationalize. There is a single time (10:45 am; sheet 1) in the table that shows a conflict when the others times do not. I loaded the whole day times are formatted the same way. I found another conflict (sheet 2). If I use the same time ranges in the morning there is no issue, but at 1:30 pm a conflict that should not exist, does. Any thoughts on this would be appreciated.
 

Attachments

  • conflict.xlsx
    37.7 KB · Views: 13
This is down to the limits/faults/accuracy of Excel's calculation.
On Sheet1 of your attachment, the formulae on row 13, let's take cell B13, refer to ranges of cells, specifically including cells A13 and N5 in bold red:
Code:
=SUMPRODUCT(--(ISNUMBER(FIND(B$1,$M$2:$M$1000))),--(((($A13<$O$2:$O$1000)*($A13>=$N$2:$N$1000))+(($A13+TIME(0,15,0)<$O$2:$O$1000)*([B][COLOR=#ff0000]$A13[/COLOR][/B]+TIME(0,15,0)>[COLOR=#ff0000][B]$N$2:$N$1000[/B][/COLOR])))>0))
A13+Time(0,15,0) should be 11am
N5 shows 11am
The formula is saying that the former is greater than the latter, when it should be saying they're not (they should be the same)
You can put a formula in any spare cell:
=($A13+TIME(0,15,0))=N5
It should return TRUE but it returns FALSE.
So how to fix?

DON'T DO THIS YET, see later…
I can only think of an ugly solution, which is to replace $A13+TIME(0,15,0) with $A14.
This assumes 2 things:
  • That your time on the next row is 15 minutes after the current row (you may however actually want to look at this time even if it isn't 15 minutes later).
  • That the your table has an extra row added with an entry in column A only, 15 minutes later than the cell above.
So this in B2:
=SUMPRODUCT(--(ISNUMBER(FIND(B$1,$M$2:$M$1000))),--(((($A2<$O$2:$O$1000)*($A2>=$N$2:$N$1000))+(($A2+TIME(0,15,0)<$O$2:$O$1000)*($A2+TIME(0,15,0)>$N$2:$N$1000)))>0))
becomes:
=SUMPRODUCT(--(ISNUMBER(FIND(B$1,$M$2:$M$1000))),--(((($A2<$O$2:$O$1000)*($A2>=$N$2:$N$1000))+(($A3<$O$2:$O$1000)*($A3>$N$2:$N$1000)))>0))
requiring 10:15 PM added in cell B59

Sheet2 was even curiouser.
I found that this was because cells A24 and O6, whilst they should be equal, weren't.
However, if I went in to edit cell A24, and then just pressed Enter, the problem disappeared.

So I went back to sheet1 and re-entered the value in cell A13, and again, the problem disappeared.

It may depend how you get the values into column A. If you autofill from cells A2:A3 down, the problem reappears - sometimes!
You may have to run down the A columns re-entering all the values (F2 on the keyboard followed by Enter is a quick way to do it) and you might see fit to do that with other time cells on the sheet.
 
Last edited:
Wow. That is some pretty crazy stuff. I tried replacing the values in A13 (sheet 1) and A24 (sheet 2) and both were fixed. I then went back and swapped data. As expected A13 then was the error on sheet 2 and A24 on sheet 1. I will fix the those two in my master table and it looks like it will take care of it. But I will keep my eye out for other problems. The are pretty easy to spot. Thank you so much again for your help!
 
Back
Top