Results 1 to 8 of 8

Thread: Tracking schedule confllicts

  1. #1
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2016

    Tracking schedule confllicts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.


    Time Day Days Start End
    M T W R F TR 8:00 9:00
    8:00 0 1 0 1 0 W 9:20 9:45
    8:15 0 1 0 1 0 MWF 9:00 9:50
    8:30 0 2 0 1 0 T 8:30 8:45
    8:45 0 2 1 1 1 WF 8:45 9:45
    9:00 1 1 1 1 2
    9:15 1 1 2 1 2
    9:30 1 1 2 1 2
    9:45 1 1 0 1 1

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,720
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    please post a sample sheet.Click " Go advanced - Manage attachments" and follow the wizard. Thanks
    Thank you Ken for this secure forum.

  3. #3
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2016
    Here is an excel file with the data above.
    Thank you.
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,760
    Articles
    0
    Excel Version
    365
    Does the attached give the right answers?
    Attached Files Attached Files

  5. #5
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2016
    Wow! That is awesome. The answers were correct (and the one I hand generated was not, .) 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.

  6. #6
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,760
    Articles
    0
    Excel Version
    365
    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)*($A13+TIME(0,15,0)>$N$2:$N$1000)))>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 by p45cal; 2020-01-09 at 11:56 PM.

  8. #8
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2016
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •