# Weekly Staff Rota Help Required

1. ## 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. ## 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
dd.jpg

3. ## 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. ## 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. ## 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

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

#### 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