Problem with work scheduling in Excel

[rdn]

Hi everyone,

I need some help with optimizing a work schedule.

What I basically have is number of workers needed for every hour during a day (FTE). Some of the work is done by regular workers and some by temporary workers hired by an external company. I want to know the minimum number of regular workers I need to keep.

Currently we work 24 hours a day, five days a week (Monday-Friday) with 3 shifts each day (2200-0600, 0600-1400, 1400-2200). The number of regular workers on each shift equals the lowest FTE during that shift:

Hour            FTE     Reg     Temp
2200-2300       19       14       5
2300-2400       19       14       5
2400-0100       19       14       5
0100-0200       19       14       5
0200-0300       14       14       0
0300-0400       19       14       5
0400-0500       19       14       5
0500-0600       19       14       5

During above hypothetical first shift the lowest FTE is 14 between 0200-0300 which means I keep 14 regular workers the whole shift (reg) and complement that with an appropriate number of temporary workers during other hours (in this example 5 temporary workes on every hour other than 0200-0300). Same goes for other shifts.

However, we're thinking of introducing another way of organizing work, which makes it no so easy to split between regular and temporary workers. The rules are (for regular workers):
- shift duration can vary between 4 and 12 hours
- each worker has to have at least 11 hour break between shifts
- workers can't work more than 40 hours a week on average
- planning is done for one month (let's assume four weeks for the sake of simplicity)

I need to find how much (if at all) the second, more flexible work system can help me reduce a regular workers count.

I don't expect a simple solution (but it would be nice to get one, no doubts ) but maybe you got some ideas on how to try to solve this? I tri
ed many ways, from splitting it down to one worker to working with whole shifts. I tried solver or VBA with no apparent success. I need a model that can cope with different FTE's.

Every help appreciated. Don't hesitate to pm or mail me if you need a spreadsheet or whatever.

Cheers
rdn

 

[shg]

Welcome to the board.

I'm not sure how to solve that, but think you need to start with 24-hour demand, not just one shift.

 

[rdn]

Thanks shg,

I actually worked with 24-hours demand as you suggested, I also tried planning for whole weeks or months. My biggest problem were always changes in shift duration. With fixed shifts it's so much easier.

My other idea was to use VBA to split a week into every possible configuration of shifts (that are within the rules) and then find the one with minimum sum of regular workers. First thought was to represent one week (or month) as an array where every position represents demand for one hour, then fill it with different number of workers to see where I get the lowest sum. But I feel that there might be to many combinations to check.

Cheers,
rdn