[Solved] How to Make Roster for shift for 21 Employee in Excel

ezraroellstoa

New member
Hi Sir, Please Help Me,,, How to Make Roster shift for 21 Employee

Schedule For 1 People = 5 Shift and 2 Off

Rule Shift

Weekday :
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People

Weekend (Saturday & Monday):
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People
 

Attachments

  • How To Make 5 Shifting.xlsx
    20.4 KB · Views: 6
Hi Sir, Please Help Me,,, How to Make Roster shift for 21 Employee

Schedule For 1 People = 5 Shift and 2 Off

Rule Shift

Weekday :
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People

Weekend (Saturday & Monday):
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People
Hi ezraroellstoa.

You can try out this formula to fill in the shift automatically based on your desired preference:
=IF(OR($D4="",F$3=""), "",IF(E4= "",D4, INDEX(Shift_Legend,IF(MATCH(E4,Shift_Legend,0)+1>COUNTA(Shift_Legend),1,MATCH(E4,Shift_Legend,0)+1))))

For this to work, you need the column E to be blank. As for Shift_Legend, I have named the shifts "Shift_Legend". This is the range A30:A36 in your sheet.

Fill this formula for all of the shift cells and it will adjust according to the starting shift automatically. However, you need to select the starting shift manually. I have added the data validation feature in these cells to select from desired shifts.
 

Attachments

  • How To Make 5 Shifting.xlsx
    38.6 KB · Views: 3
Last edited:
Hi ezraroellstoa.

You can try out this formula to fill in the shift automatically based on your desired preference:
=IF(OR($D4="",F$3=""), "",IF(E4= "",D4, INDEX(Shift_Legend,IF(MATCH(E4,Shift_Legend,0)+1>COUNTA(Shift_Legend),1,MATCH(E4,Shift_Legend,0)+1))))

For this to work, you need the column E to be blank. As for Shift_Legend, I have named the shifts "Shift_Legend". This is the range A30:A36 in your sheet.

Fill this formula for all of the shift cells and it will adjust according to the starting shift automatically. However, you need to select the starting shift manually. I have added the data validation feature in these cells to select from desired shifts.
thank you for your help sir.

But,

How do you make sure that every day the number of employees who shift automatically is as follows

Weekday :
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People

Weekend (Saturday & Monday):
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People
because from the countif results the number of shifts is still the same
1699198040468.png
 
thank you for your help sir.

But,

How do you make sure that every day the number of employees who shift automatically is as follows

Weekday :
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People

Weekend (Saturday & Monday):
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People
because from the countif results the number of shifts is still the same
View attachment 990
Hello again!

Usually, the shift rosters are set up to cycle the shifts of each person. The way you want, it is not possible to follow this pattern. i.e. if an employee has to start in the 2nd shift he may end up working in that shift for several days in a row.

And even in that scenario, the assignment ends up pretty random as some employees need to work particular shifts more than others. This is also applicable for off shifts. In that case, it is tough to put the distribution in a single formula without further conditions, such as who is likely to get what shifts more.
 

Online statistics

Members online
0
Guests online
28
Total visitors
28

Forum statistics

Threads
278
Messages
1,198
Members
473
Latest member
tu2815
Top