Forum Discussion
Roster Conflict
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 ?
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...
18 Replies
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.
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
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 🙂
