Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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]
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    can you offer any advice?

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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