Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shift Pattern (2003 SP2)

    Good afternoon

    I am trying to design an automatic Excel shift pattern sheet and failing badly.

    I have 3 shift patterns (A) 07:00 - 16:00 ([img]/forums/images/smilies/cool.gif[/img] 09:00 - 18:00 10:00 - 19:00 and five staff members, each work week is Monday - Friday shift (A) has one person everyday and ([img]/forums/images/smilies/cool.gif[/img] and have 2 people which have to fairly rotate.

    I have made a workbook but it is very messy and needs a lot of user input, can anybody suggest a more dynamic way to achieve this

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shift Pattern (2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 11-Oct-07 11:02. Added PS)</P>What is wrong with your current scheme: A to B1, B1 to B2, B2 to C1, C1 to C2, C2 to A?

    It is simple and predictable. Everyone will know what shift they are on for the entire year.

    Steve
    PS If you do not want to repeat B and C another pattern is:
    A to B2 to C1 to B1 to C2 and then back to A.

    I guess it comes down to preference. Have you discussed the pattern with the people who are affected by it?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shift Pattern (2003 SP2)

    Edited because my fat fingers seem to have mis arranged the text

    Hi Steve

    Sorry if I did not make myself clear, I was looking for a formula, I guess, that I could copy and paste that would rotate them through the different shifts. At the moment as I go down a row to the next 4 weeks I have to manually type in the next shift pattern as it will not copy down the page.

    Although this is not it, I kind of figured that if in a different part of the worksheet if I had a list of the employees names I could copy and paste a formula that said something like if employee Veronica=shift A this week next week would be b etc.

    But thanks for looking I will plod on as I am

    Thanks

    Steve
    Cheers

    Steve

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shift Pattern (2003 SP2)

    If you're willing to forego weeks next to each other, it's quite simple - see attached version.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shift Pattern (2003 SP2)

    How about this. Copy the items from A9:B9 down the rows.

    [The items from C9:E14 are copied into C1:E6.]

    A1 has the starting Date (it is hidden). Change this and all the dates will change.
    A2:A6 has the Shift designations (change this and all will change)
    B2:B6 has the Starting name list (change this and all will change)

    Steve
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shift Pattern (2003 SP2)

    Thanks to Both Steve and Hans

    I have plenty to play with here

    Thanks

    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
  •