Forum Discussion
Roster Conflict
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...
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 🙂
"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
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.
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...
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
