Weekly Staff Rota Help Required

**zouraiz** · 11-30-2013, 08:41 AM

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.

**grkchakri** · 12-01-2013, 12:20 AM

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

**zouraiz** · 12-01-2013, 02:58 AM

Dear grkchakri,

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

**grkchakri** · 12-01-2013, 08:37 AM

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

**zouraiz** · 12-01-2013, 12:15 PM

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