Hi all,
Did anyone on this forum ever try to create a excel sheet which would allow to create sophisticated schedules for employees? By sophisticated i mean schedules with:
- A requirement forecast (e.g. 6:30 - 2 Heads needed, 7:00 3 Heads needed ... 14:00 15 Heads needed etc.).
- Multiple Shift lengths - 4,5,6,7,8,9 hrs length
- Start times (e.g. a given shift can start only at 8:00, 9:00 or 10:00 while another one can start at 8:00, 8:30, 9:00, 9:30 etc.)
- Hrs in a week - the sum of all shift lengths assigned by the vba code should equal a given number (e.g. 40).
- Start time consistency - If a given shift starts at 8:00 on Monday, it can start only within +-1hr during the rest of the week.
I imagined a combination of excel functions/formulas and some vba code to to the job. The biggest "concern" lies in the code that would assign the individual shifts - Lets say that the head(hours) requirement equals 100 working hours on a monday and has a normal distribution. Someone has created the shift rules etc. already and assigned them to "names". It turned out that there can be only 80 hours distributed on that monday (10x8hrs shifts to make it simple). The vba code would have to figure out that there are not sufficient hours to distribute to cover the requirements completely and would have to make the best of it anyway (e.g. not leaving the last 2 hours without any schdules - that would be an easy mistake if the code goes from the morning till evening while distributing shifts etc.).
I'm sure that there are commercial applications that do such tasks. I'd like to try to create something on my own though. I'm not asking for anything specific - just some thoughts on the problem whether experienced people think that this is doable in excel - or if I shouldn't waste my time on such a task). Perhaps someone did indeed try to make something similar work? Or perhaps someone has worked with some algorythms that would make it easier to create the part that would generate the best possible schedule with the given constraints?
Thanks for your time!
Bookmarks