Calculating Number of Staff for Makinf a roster

M

mazher

Active Member
Joined
Nov 26, 2003
Messages
359
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Excel Gurus,
I am stucked on this, hope someone can help

Office is open 365 days a year

Shifts run each day
Mon - Thursday
02:00 to 10:00 (3 persons in shift)
10:00 to 18:00 (3 persons in shift)
18:00 to 02:00 (3 persons in shift)

Except for Friday & saturday
02:00 to 10:00 (3 persons in shift)
10:00 to 18:00 (3 persons in shift)
18:00 to 02:00 (5 persons in shift)

No of staff duties = 67 for a week

Each person is allowed 1 day off in a week and is entitled to 14 days leave in a year.

I need to calculate the total number of staff required to cover day off in a week and the 14 days leave.

Thanks in advance.

Maz
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You give the plan of shifts for Mon-Thu and Fri-Sat. What about Sun?

J.Ty.
 
Upvote 0
Sorry my mistake,
the plan is

1) Mon-Thursday & Sunday
2) Friday-Saturday
 
Upvote 0
J.Ty. said:
You give the plan of shifts for Mon-Thu and Fri-Sat. What about Sun?
I'm guessing
Mon - Thursday
02:00 to 10:00 (3 persons in shift)
Except for Friday & saturday
18:00 to 02:00 (5 persons in shift)
has some overlap on the Sunday
J.Ty.
Click to expand...
ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
 
Upvote 0
Hi,

* Without day off and without 14 days of leave you need 11 people, because there are 11 shift units to do on Fri and Sat, and (I assume) you cannot force any of your employees to do 2 shifts on one day.


* With day off every week and with 14 days of leave you need 12 people.

The weekly plan is as follows:

You choose employee X who will get 3 days of annual leave this week, and employee Y, who will get 1 day of his annual leave this week.

On Fri and Sat employee X gets 2 days of annual leave, 11 remaining people work on those days.

On Sun you give X and additional day of annual leave (his 3rd this week), Y gets 1 day of his annual leave, 9 people work. You have one spare person who could work in the office, but for whom you have no shift to do.

Every day from Mon to Wed you need only 9 people, so 3 of them can stay at home. Within 4 days this gives everybody their weekly day off.

Every employee plays the role of X for 4 weeks (12 days of leave) and then plays the role of Y for 4 weeks (4 days of leave), while the next person is X. This gives him 16 days of leave, more than you need, so you can decide what to do with those 2 extra days.

12 people are X for 4 weeks each, so within 48 weeks everybody has got the annual leave days. Only the last person completes as Y in weeks 49 and 50, still before the end of the year.

Finally, the answer is: you need only 1 extra person to cover the weekly day off and the annual leave for everybody.

Best regards,

J.Ty.

P.S. Your question has nothing to do with Excel.
 
Upvote 0
J.Ty. said:
Hi,

* Without day off and without 14 days of leave you need 11 people, because there are 11 shift units to do on Fri and Sat, and (I assume) you cannot force any of your employees to do 2 shifts on one day.


* With day off every week and with 14 days of leave you need 12 people.

The weekly plan is as follows:

You choose employee X who will get 3 days of annual leave this week, and employee Y, who will get 1 day of his annual leave this week.

On Fri and Sat employee X gets 2 days of annual leave, 11 remaining people work on those days.

On Sun you give X and additional day of annual leave (his 3rd this week), Y gets 1 day of his annual leave, 9 people work. You have one spare person who could work in the office, but for whom you have no shift to do.

Every day from Mon to Wed you need only 9 people, so 3 of them can stay at home. Within 4 days this gives everybody their weekly day off.

Every employee plays the role of X for 4 weeks (12 days of leave) and then plays the role of Y for 4 weeks (4 days of leave), while the next person is X. This gives him 16 days of leave, more than you need, so you can decide what to do with those 2 extra days.

12 people are X for 4 weeks each, so within 48 weeks everybody has got the annual leave days. Only the last person completes as Y in weeks 49 and 50, still before the end of the year.

Finally, the answer is: you need only 1 extra person to cover the weekly day off and the annual leave for everybody.

Best regards,

J.Ty.

P.S. Your question has nothing to do with Excel.
Click to expand...

Thanks for your explanation

I need to calculate this and need to make a rota in excel for the whole year.

But I have calculated the number of staff 14 for completing this rota as follows , hopefully mathematician excel gurus will correct me if I am wrong

5 days x 3 shifts x 3 persons x 52 x 8 hours = 18720
2 days x 3 shifts x 5 persons x 52 x 8 hours = 12480
Total Hours = 31200

Total days in a year a person can work = 364 - 14 annual Holidays - 52 weekly holidays = 298 days x 8 hours = 2384

Total nuMber of staff required = 31200/2384 = 13.12
So I need a 14 satff members to complete a above rota.

Hope some one shed some more light on this.

Thanks

Maz
 
Last edited:
Upvote 0
mazher said:
But I have calculated the number of staff 14 for completing this rota as follows , hopefully mathematician excel gurus will correct me if I am wrong

5 days x 3 shifts x 3 persons x 52 x 8 hours = 18720
2 days x 3 shifts x 5 persons x 52 x 8 hours = 12480 <--- This is wrong. On Fri and Sat only 1 shift needs 5 people.
Total Hours = 31200
Click to expand...

You have made a mistake - see above.

J.Ty.
 
Upvote 0
one small thing a year is 365.25 long to allow for leap year. I'd be nervous saying there are 52 weekly holidays. Do you have upper weekly hours that people are contracted to, do you have team supervisors built in, or is everyone autonomous, any minimum time between finishing and restarting work, are your staff productive when starting at 2am
 
Upvote 0
Thanks J.Ty.

I have alo realized my mistake.

J.Ty., Can u please implement this in a excel to make a rota.

Thanks again in advance.

Maz
 
Upvote 0
Dear Maz,

Please download the rota from here:
http://www.mimuw.edu.pl/~jty/MrExcel/Mazher.xlsx

Empty cells mean "Work", "Leave" means annual leave, "Day off" means weekly day off.

The rota is organized into 48 weeks (less than a year), during which:


  1. Every day there are as many people assigned to work as many shifts you need that day. I leave the problem of assigning individual shifts to people for you.
  2. Everybody gets 16 days of annual leave and one day off every week. You should decide how to handle the excess of 2 days of leave over the requested 14, because there are no free shifts for those people.
  3. Additionally, every Sunday you have a single person assigned to work, but no shift for him/her. You should decide how to handle this.
  4. The rota ends in slightly less than one year. You probably should start it then over, but bear in mind that this will create still more leave days over the requested 14. I guess your employees will not complain ;)

Good luck!

J.Ty.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
74
ChetanPuri
C
J
  • Question
Shift bands
2 3
Replies
29
Views
990
felixstraube
F
M
  • Question
Complexed Time Difference Formula
2
Replies
17
Views
1K
mahmed1
M
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
265
AOB
AOB
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
670
felixstraube
F

Forum statistics

Threads
1,214,875
Messages
6,122,040
Members
449,063
Latest member
ak94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top