Roster Conflict

[Cambosity]

Hi all. I have a sample of what i'm looking for. That is I would like to get a formula for conditional formatting that highlights when there is a conflicting shift as in this sample. D4 and E16 both have A as staff member but different clients. I have had a crack at it in L2 but it doesn't work. Anybody with ideas ?

[#2]

@Cambosity 

Can I ask for an explanation of the logic behind the (widely) differing hours given to what are labeled as the same shifts? That internal inconsistency certainly complicates what you're trying to do.

For example, Staff Member A has Shift 1 as going from 6:00 to 12:00 (and none of the others have Shift 1 as showing hours like that, although they too vary all over the place) ... so it would appear that Shift 1  itself has no set meaning.

And then looking at Staff Member A in Shift 2, which in that case--here's your conflict--overlaps A's shift 1 but not necessarily that of others.....

Can you help make sense of that layout, first? Then maybe we can come up with a formula or function to highlight the clashes.

[#3]

@mathetes 

Yep I can explain. I work as  an NDIS provider. We supply support for people with disability. Every participants plan is individualized according to their particular needs so the rostering is complex. We currently have 50 participants and 35 staff and due to potential unforeseen issues on a weekly basis the roster week to week is a living document. There are commonly unexpected appointments with doctors, specialists, therapists or dealing with behavioral issues relating to self harm. 

So in dealing with these issues with a pool of casual Disability Support workers it would be great when an issue arises (which is every week) we can rearrange the roster and automate identifying potential roster conflicts. Hopefully this illuminates the issue. :) Thank you for looking at this :)

[#4]

@mathetes 

To further explain the pay rate is consistent from 6:00am to 8:00pm. It is a requirement for a casual staff member to complete 3 hours on a single day but shifts may be only 1.5 hrs occurring anywhere in in this time frame. It is common for a staff member to do 2,3 or 4 shifts beginning anywhere in this time frame of any length up to 8 hrs but no less than 1.5. So this means a 1st shift may occur at 3:00pm for example. Hope this clears it up :)

[#5]

@Cambosity 

"Clears it up" might be overstating things, but what you've written is definitely a move in the direction of greater clarity.

The problem here, I think, is that the sheet you're working with (the one you posted a few days ago) is more of  what I'd describe as an output sheet--it can be posted on a bulletin board (physical or virtual) to show assignments clearly. But the way it's arrayed is more for human consumption than further computer manipulation.

I'm starting to visualize a table (spreadsheet)  of "raw data" that shows Staff Member A's (and B's, C's, etc) assignments in whatever the smallest increments of time are.  Is that smallest increment 30 minutes or 15? Or less.

I saw that the smallest assignment is 1.5 hours, but does it go up from there by 30 minute increments or 15 minutes?

And then that raw data could be the basis for the "prettier" summary that is posted; it could also be the basis for quick identification of the schedule clashes. It's trying to accomplish both tasks with what is essentially a final output....that's what's making this difficult.

Easier to do when meeting in person than through an exchange of message board postings. But were we to be meeting in person, I'd want to back up in the process and ask such things as:

• how each assignment of client to staff member gets made in the first place;
• how are the lengths of times determined, along with the corresponding days and start and finish times.
• is there a central scheduler or do the staff members just tell you what they're doing and when
• etc

[#6]

@Cambosity  I agree with @mathetes comments about this being a human readable sheet and such, but I think I get what and why your are doing it.  That said in the attached I did 2 things:

I updated the sheet where you list the options for the data validation (drop down list) so that for each shift you can only select an employee that hasn't been selected yet for that shift but was selected for a previous shift (i.e. "A" can't have a shift 1 and then a shift 3 without being assigned a shift 2 and "A" can't be assigned 2 shift 1's)

I created the conditional formatting that looks if the start of this shift begins before the previous shift ended.  I only highlighted the "new" shift that created the conflict but could easily highlight the previous shift also if needed.

I hope that helps you and your team keep doing good things for these people.

[#7]

@mtarler Thank you so much

This is exactly what I have been looking for. And yes if this can be applied to the previous shift so that it can be identified that would be amazing. Thank you so much this saves hours of manual cross checking.... Your formula improves our ability to effectively supply service to individuals in genuine need. Thank you

[#8]

@Cambosity  updated

[#9 · Accepted solution]

@mtarler 

OK just looked further into your amendments. Restrictions on the data validation go to far. For example if I needed to give Staff H a 2nd or 3rd shift they would not appear in the drop down box.Also I J and K as casuals may not be available for shifts this week... Mind you if it displayed potentially available staff, shift by shift,  in the drop down box it would be incredibly powerful. I am daily asked to find ways to cover shifts due to staff illness / unavailability or pop up shifts from unexpected appointments with participants... And yes it would be excellent if the highlighting occurred on both examples of the conflict. Thank you so much your formulae will actually have a real positive impact on lives in our society...

[#10]

With @mtarler I commend you, @Cambosity , on the good work you are doing for people very much in need.  My youngest brother (who passed away last year at the age of 70) was one in that NDIS client community, so I know firsthand the importance of the work.

I also want to compliment @mtarler  on the good Excel work he's done here to help you. He was able to see through the stuff that just raised questions in my mind. An example of how great this techcommunity is.

[#11]

@Cambosity Ooops.  It didn't go "too far" it was just wrong. lol.  I thought I had a clever shortcut using the previous list but that was just wrong.  The new dynamic link is a more complicated formula but I believe it is correct now.

[#12]

 @Cambosity 

Sorry it already does apply to both shifts.... And it works beautifully... :)

[#13]

@mathetes 

My condolences in regards to your brother.... 

[#14]

@mtarler 

I think something went awry with my last message... Drop down lists are still not quite right. They are giving staff that will still be in conflict. Maybe if we have more staff in the sample ? I have increased the staff to the letter t making it B21 and adjusted the formula accordingly but it is still giving me the wrong staff. Any chance another look... ? :) The conflict conditional formatting is brilliant !!

[#15]

@mtarler 

Also here is a copy of the actual roster just cleaned of recognizable data. I could not work out how to insert your formula LOL way to advanced for me !

[#16]

@Cambosity The drop down seem to be working correctly for me.  They do NOT stop conflicts since that would depend on the time that you are going to enter. Re-looking at this chart I might see my misunderstanding but not sure what to do about it.  So the drop down check 2 conditions:

1) the same staff provider can NOT be selected 2x for the SAME shift

2) a staff provider must have a previous shift to be selected for the shift.  i.e. staff member "A" must have a Shift 1 before they can be selected to have a Shift 2

I think my problem here is that those "shifts" are CLIENT shifts NOT staff member shifts.  Therefore, I suppose a staff may be added to the "3rd shift" for a client even though it is THEIR second shift.  Here is a new conditional formatting formula that I think will work for this general case (based on the original sample sheet):

=SUMPRODUCT((C3>" ")*(C3=$C$3:$I$21)*(C4>$C$4:$I$22)*(C4<$D$4:$J$22))+SUMPRODUCT((C3>" ")*(C3=$C$3:$I$21)*(D4>$C$4:$I$22)*(D4<$D$4:$J$22))

 I will try to apply this to this updated sheet you posted, but there is a LOT going on here and I'm not sure I understand it all.  That said you have some CRAZY nested IF statements.  I bet you could make them much more simple based on an array but at the VERY LEAST you can use IFS() function. So I adjusted and applied that formula to RWk43 Monday columns. See attached.  BTW, I dropped that custom data formatting/drop down lists because that would be a huge mess with so many days/weeks/sheets/etc... and probably doesn't help with what you need it to do anyhow (based on my comments above)

[#17]

@mtarler 

Amazing ! The conflict thing works perfectly I have applied it successfully to my active sheet and it is just brilliant.... Also, after thinking it through (I reckon I got over excited with possibilities), I think it best as it is with all staff selectable for each shift as it allows the rosterer choice. A shift conflict can be resolved in 2 ways. 1/ By changing the staff member or 2/ slipping the shift, so a 3hr shift from 9 til 12 might become 11 till 2. This is not always possible but often is depending on the flexibility of the NDIS plan.

I had a crack at applying it to Tuesday but must have done something wrong came out all buggy finding conflicts where there were none.

Have noticed 1 Monday bug. E14 does not highlight. Its counterpart conflict will. 

Also thanks for the tip re IFS and arrays....yep there were some crazy hours put into my nested IF hehe... wish I'd known about them when I started LOL !!!

If you look to the right of the screen you see staff hours totals. Would it be possible to have those accruing totals  in the drop down list next to the staff. Once again it would save scrolling backwards and forwards across the screen and aids in staff selection as some staff are permanent part time anywhere between 10 and 30 hrs and others are casual ?

Once again I must let you know of my gratefulness. Thank you I am learning heaps in the process as well... :) Cambo

[#18]

@Cambosity  OK see attached version.  I realized I had an error in concept.  I flagged an overlap based on either the start time or end time being between the start time or end time of another entry, but that doesn't include an overlap when 1 time frame is completely encompasses another timeframe.  So I changed the formula and think it works better now.  It now flags anytime the start time is before the end time and the end time is after the start time of any entry that is not itself.

As for the staff time I'm not sure which column you want and what 'drop down' you are thinking but assuming it is on that weekly sheet, it has a list of staff on every other row so you could use the odd rows to show that value.  

If you copy the upper left corner cell it will also copy the formatting over, but the 'fixed ranges' will still need to be updated, or copy the formula and paste it.  Either way the key cells/ranges are the upper left corner, the cell 1 row below, the cell 1 row below and 1 column over, and similar the ranges get offset down 1 row and down 1 row and over 1 column.  Just compare with the previous and do the same.  So here is the formula:

=SUMPRODUCT((E4>" ")*(E4=$E$4:$K$136)*(E5<$F$5:$L$137)*(F5>$E$5:$K$137)*((ROW(E4)<>ROW($E$4:$K$136))+(COLUMN(E4)<>COLUMN($E$4:$K$136))))

where:

UPPER LEFT CORNER = E4

Down One = E5

Down and Right = F5

Range A = $E$4:$K$136

Range Down One = $E$5:$K$137

Range Down & Right = $F$5:$L$137

Hope that helps break it down so you can apply it to the other ranges.  I could create a formula that applies to the entire range but I'm concerned that the sheet is already acting a bit slow due and that would cause even more unnecessary lag.  That said I suggest deleting those conditional formatting formulas that apply color based on columns since that isn't 'conditional' and you should just apply that fill colors to those columns instead.  I also suggest finding other areas that formulas can be simplified or removed.  

Best of Luck

[#19]

@mtarler 

Sorry for the slow reply. Formula works perfectly. Thank you. Am in process of replacing all those IF formulas with arrays and IFS. Great idea with the spaces in the drop down lists! Learning heaps. I hope you had an excellent Christmas :)

Kind regards