The only actual benches that need to be filled are: OE (2), 4A, 4B, A1C/OE, APS, ARC, BF, BG, CA, DG, MC1-4, SYS, UA (which numbers 18). The excel file is taken from a completed upcoming schedule and I have removed the employee's names for confidentiality. The TRN benches are designated training shifts. The two trainees in the sheet are night shift people not part of our core shift employee pool that we are training. As part of their training they work the bench unsupervised for usually a week after the TRN shifts, and then thus become part of our pool of coverage, often resulting in excess people on a shift. These people are considered "floaters" assigned an extra bench "CS/FL," or "MC2/FL." These are not part of the core, necessary benches. "Off" is also not a bench, but peoples elected ("OFF/P," preferred) day off or variable off day (designated as"OFF," can be shifted if needed). PTO is for "Paid time off" and is not a bench. OS is for operational supervisor, which is the person in charge of the lab on a given shift. I trade off this bench with the other supervisor on my shift. It does not need be checked, however, because those responsibilities are assigned to a senior tech on a bench (this is when you see /TIC) on the weekend/holiday. "Incomp" is being generated by formula I was trying to use to check if those 18 core benches had been filled. I really am trying to focus in on checking those core 18 benches listed in the beginning.
So here is our process from the beginning. 1) We open a two week template spreadsheet 2) We input the dates of that pay period and save it as that pay period's schedule 3) We input PTO and preferred days off from a calendar (sounds old fashioned but I actually think this part works OK) 4) We look at the training rotation, someone is always in need of some cross training and there is a long queue. If they are a part our core shift pool, training removes them from availability for the core benches, and also determines which bench the will work upon completion of the training. 5) On the blank template each employee is a assigned a "1" on a given day that they are working. This is summed up at the bottom of the spreadsheet. If one is replaced by anything (PTO, OFF, TRN ARC, BG etc.) It subtracts from that sum. If that number is less than 18 after we have assigned PTO, off days and training, we know we have a shortage that needs to be addressed. 6) From there we have a checklist of those 18 benches and we start manually inputting them and checking them off as we go.
It is really step 6 that I'd like to address. It's very easy to make an error here and miss a bench, even with multiple people proof reading the schedule. Errors still make it through and we have on occasion not realized a necessary bench wasn't scheduled until the day of the shift. Also as a matter of efficiency steps 1-5 take 10-15 minutes altogether. Step 6 and the subsequent proof reading take considerably longer.
Thanks to whomever stuck with me to the end of this post, as it really could be tldr.
