Complicated scheduling problem
First, thanks for your help in advance. Now on to my problem/situation.
I am responsible for creating a complicated task schedule for a shift of volunteer workers every week. I am currently doing this by hand (well, excel really) and it is a bit tedious. I inherited this job from the previous guy who apparently did not mind doing this by brute force every week, but I am looking for something a little more automated. I am looking for some tips or possibly even a template starting point to help me. Any and all advice is appreciated. Here are the details:
I am scheduling for a volunteer shift every Saturday night. Many of the volunteers come every week, others come every other week, some once a month, etc. Always there are people who call me up on Friday and say "I won't be there tomorrow even though I am scheduled to be".
Each volunteer has been trained for different jobs. Some have been trained and know how to do all the jobs, some can do only one job, most can do multiple jobs.
I schedule each volunteer to do multiple tasks during the shift as no one likes to do the same thing all night. I have broken the shift into 30 min increments but usually try to schedule one particular task for at least an hour. There are certain jobs that take more time, for example 90 minutes, and the volunteers cannot multi-task due to the nature of the jobs. In particular, I have to schedule a 90 minute task starting every 30 min(i.e. different people doing the same task, but starting at different times). A good analogy is a 90 minute museum tour with a new group that starts every 30 minutes. The tour guides need to be scheduled for the 90 min tour and multiple guides need to be scheduled so that each tour is covered.
Other tasks can take as little as 30 min and I usually fill those tasks in around the core 90 min tasks. But I need to have a minimum number of people doing certain tasks at any given time. For example, Job 1 takes 90 minutes and a new instance of Job 1 needs to start every 30 minutes. Job 2 only takes 30 minutes but there will be many instances of Job 2 and I need a certain minimum number of people doing Job 2 at any given time. Job 3 is similar to Job 2. So I would normally schedule the big 90 min Job 1s for the shift then fill in the Jobs 2 & 3 around those so that the minimum # is covered and no one is doing say Job 2 for more than 1 hour or Job 3 for more than 2 hours before switching to a different job. They could come back to Job 2 or Job 3 after doing something else for a time.
Finally, sometimes I need to specify a specific worker to do a specific task at a given time. Other tasks I am ok with having them assigned randomly as long as the above criteria are met.
What do you guys think? Would this be a job FM could handle or should I look for a more dedicated scheduling software?
I know this is probably a pretty involved project. I do know some level of DB management, but I'm not the most proficient. I thought if I could find a good scheduling template to start from, I could modify it to suit my needs.
Thanks again for your help.