+ Reply to Thread
Results 1 to 5 of 5

Weekly Staff Rota Help Required

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    riyadh
    MS-Off Ver
    excel 2010
    Posts
    24

    Weekly Staff Rota Help Required

    Hi There,

    I need help to create a weekly staff rota. As supervisor open the worksheet the dialogue should appear asking to put number of Shifts (1 or 2) and number of People working (1-6). Based up the user input data the excel creates a staff rota.

    Criteria to look

    Shift 1 = 8:AM - 8:PM (12 hrs Straight)
    Shift 2 = 8:AM to 12 PM & 4:PM to 8:PM ( 8 Hrs)
    No. of Hours staff required to work per day = 8 Hrs.
    No. of days staff need to work in one week= 6 days

    Output should be

    Staff 1 Hours need to work in week days and day off
    Staff 2 Hours need to work in week days and day off
    Staff 3 Hours need to work in week days and day off and so on...

    I really don't know how to start please help me to create how do i set formulas in excel sheet. Appreciate your thoughts in these matter.

  2. #2
    Forum Contributor
    Join Date
    02-12-2011
    Location
    Hyderabad
    MS-Off Ver
    office 2007
    Posts
    243

    Re: Weekly Staff Rota Help Required

    Hi
    Just to give a heads up

    We can start doing by putting option buttons . I have just attached a screen shot let me know if this is how you think the rota would be

    It was just a thought.

    The idea is all 6 persons will have only 48 hours(6days*8hrs) in a week so we can compare the values in RANGE."B2" with RANGE ."O2"

    Whenever we choose option button1 it considers it as shift 1 12 hours and next shift 2 as hours

    Finally it sums up in range("O2") for Staff 1 and so on for other employees. so we can check if we have met our target of
    6 employees * 8 hrs day * 6 days = 288hrs

    Let me know your inputs we will proceed accordingly

    The code can be something like below
    Please Login or Register  to view this content.
    dd.jpg
    Last edited by grkchakri; 12-01-2013 at 12:23 AM.

  3. #3
    Registered User
    Join Date
    12-04-2012
    Location
    riyadh
    MS-Off Ver
    excel 2010
    Posts
    24

    Re: Weekly Staff Rota Help Required

    Dear grkchakri,

    I really like your idea please provide me the complete concept through excel sheet. Appreciate your support..

  4. #4
    Forum Contributor
    Join Date
    02-12-2011
    Location
    Hyderabad
    MS-Off Ver
    office 2007
    Posts
    243

    Re: Weekly Staff Rota Help Required

    Just looking at your question you said 8 hours per day but shift1 has 12 hrs of working hours.

    So clarify if a employee chooses shift 1 do they work 12 hour straight or do they choose work 8 hours in shift1 and work in shift 2
    for the remaining 4 hours the next day as they have completed 8 hours the preceding day

  5. #5
    Registered User
    Join Date
    12-04-2012
    Location
    riyadh
    MS-Off Ver
    excel 2010
    Posts
    24

    Re: Weekly Staff Rota Help Required

    Dear grkchakri,

    yes it true weekly contract is 48 hrs.Lets add one more shift called shift 3
    Shift 1 = 8-5
    Shift 2 = 10-7
    Shift 3 = 12-8

    Assuming that 1 hr lunch will be deducted from the hours

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Staff rota
    By chelfox in forum Excel General
    Replies: 1
    Last Post: 10-07-2013, 11:25 AM
  2. [SOLVED] Some shifts wont add on my staff rota weekly total
    By bigtiger1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2013, 09:53 AM
  3. Staff Rota
    By parkey5 in forum Excel General
    Replies: 5
    Last Post: 07-14-2013, 04:16 AM
  4. Staff rota
    By goofy14you in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2007, 04:13 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1