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

[ezraroellstoa]

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

 

[Abrar-ur-Rahman Niloy]

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.

 

[ezraroellstoa]

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

 

[Abrar-ur-Rahman Niloy]

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.