1. Good morning

I have tried, and failed spectacularly, to manually allocate each team member to shifts A-F in a fair and rotating manner (the initials next to the months are the team members), my head is numb from banging it on a brick wall!!

Can anybody help perhaps with;

1. a formula
2. a macro
3. or a better suggestion for the WS layout for it to work

Cheers

Steve

2. Does the attached workbook do what you want?
I created one long table for the entire year, used formulas for the shifts and conditional formatting for the background colouring.

[attachment=82647:shift1.xls]

3. [quote name='HansV' post='763551' date='05-Mar-2009 10:14']Does the attached workbook do what you want?
I created one long table for the entire year, used formulas for the shifts and conditional formatting for the background colouring.

[attachment=82647:shift1.xls][/quote]

Thanks Hans

You are truly a genius that seems to be perfect

Thank you

Steve

4. Evening all

In my original post attachment I made a mistake with shift C and over the fortnight period made it a 5 and 6 day shift. As a result the avergae number of days off for each team member was about 97 whereas there are 104 weekend days in the year.

Just by making this amendment of one day in a fortnight it adjusted the average days off to 121 days so when you factor in weekends and the 8 statutory UK Bank / Public holidays the team members get a bonus of 9 extra days of as a kind of shift allowance.

I have attached the amended workbook incase it is of any use to anybody.

Cheers

Steve

5. Hi Hans

Sorry to impose but I have tried to adapt the formula to another department that has 4 members and 4 different shifts. Following your exampe I have arranged the shifts and calander and used this formula

=OFFSET(\$S\$2,MOD(ROW()-20,14),INDEX({0;2;1;3},MOD(INT((ROW()-20)/14+MOD(COLUMN()-2,4)),4)+1))

It works but I do not know if it is working properly as I do not undertand what the MOD(ROW()-20,14) and the MOD(INT((ROW()-20)/14+MOD parts of the formula do.

Do I need to change them to make it work for 4 instead if 6 shifts and 6 shift members?

Cheers

Steve

6. [quote name='stevehocking' post='763750' date='05-Mar-2009 22:20']Hi Hans

Sorry to impose but I have tried to adapt the formula to another department that has 4 members and 4 different shifts. Following your exampe I have arranged the shifts and calander and used this formula

=OFFSET(\$S\$2,MOD(ROW()-20,14),INDEX({0;2;1;3},MOD(INT((ROW()-20)/14+MOD(COLUMN()-2,4)),4)+1))

It works but I do not know if it is working properly as I do not undertand what the MOD(ROW()-20,14) and the MOD(INT((ROW()-20)/14+MOD parts of the formula do.

Do I need to change them to make it work for 4 instead if 6 shifts and 6 shift members?

Cheers

Steve[/quote]
The 14 refers to the 14 days the shifts last. If you had a 3-week shift, you'd use 21 instead of 14.
The 20 is because the roster begins somewhere near row 20 (I don't have the sheet in front of me at the moment); if the table started lower down, the number would be higher and vice versa.

7. Hi Hans

Thanks for the explaination. Sorry to bang on with this but I can't seem to get my head around some of the logic, I can now understand (I think) the formula and how it is working.

I am trying now to change the shift pattern to 4 rotaing shifts and 2 fixed shifts, so it is now only shifts A, B, C and D that concern me. The main criteria is that there is always somebody (can only be one person) working on a Saturday and the same for Sunday.

By playing about with the starting row I have managed to achieve this criteria but the B shift confuses me

This is giving a repeated pattern of off, off, on, off, off and I don't think anybody would want that type of shift pattern. I can't help thinking that it is the way that I have laid the shift matrix out and that is where the logic gets me. I have tried starting the matrix on different days and in the formula changing the starting row but by doing this I lose the one thing I have got right, the Saturdays and Sundays.

Cheers

Steve

8. Having fewer rotating shifts makes it more difficult to obtain a 'fair' distribution. This is marginally better:

=OFFSET(\$D\$2,MOD(ROW()-23,14),INDEX({0;1;2;3},MOD(INT((ROW()-23)/14+MOD(COLUMN()-2,4)),4)+1))

or

=OFFSET(\$D\$2,MOD(ROW()-23,14),INDEX({0;1;3;2},MOD(INT((ROW()-23)/14+MOD(COLUMN()-2,4)),4)+1))

It's unavoidable that people sometimes have only one day off between shifts Going to C from any of the others), and sometimes four days: if you want to avoid going from C to B (which would cause off, off, on, off, off), you must go from C to either A or D.

9. Hi Hans

Thanks for that, I had not realised that this part of the formula {0;1;3;2} related to the order of A-D, I was playing with the other settings and not that.

Cheers

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•