Excel Shift Roster Generator

Excel Shift Roster Generator helps create a work schedule or roster for pre-established shifts that rotate weekly for staff resources in a team or entire business. It requires defining work shifts start and end times according to the company and/or country policies, which will apply to the list of resources available. The roster displays colour-differentiated shifts along the calendar days for each resource available.

Resources

We can add and delete resources either manually or using the respective buttons at the bottom of the Console worksheet. Resource names should be updated as necessary. Excel Shift Roster Generator allows to add a total of 20 resources. Each resource is supposed to be 1 Full Time Equivalent (FTE), and that cannot be changed in this version. The FTE can be defined at the bottom by setting the working hours per day and labour week days. This is usually 8 hours per day five days a week (40 hours per week), but it may be different for some countries.

We can delete all resources at once using the Clear button. Please note this cannot be undone. Also, this will not reset the FTE definition at the bottom, so any changes to that will remain.

Shifts

We can add and delete shifts either manually or using the buttons at the bottom of the Console worksheet. A simple 8 hours shift from Monday to Friday (total 40 hours per week) requires a single row. But we may need to add more than one row for some shift combinations. That’s necessary for example if we want to add days off within the week or any shift that does not have a continuity of week days. Also, that would be necessary if a shift is changing start or end time within the same week.

We can also specify the number of FTE required for a given shift. That means the number of resources that will work on that particular shift. Despite the Excel Shift Roster Generator accepts decimals here, this version does not support decimal combinations of FTE resources.

The Show Shifts command button displays the summary weekly view for each shift in other worksheet (Shifts). Each shift has a different color to be easily distinguished. The total number of hours and FTE required are indicated at the end of the table. At the bottom, there is a summary for the total number of shifts, FTE requirement, and available resources. The balance between the total FTE requirement and resources should be zero.

Shift information can be manually amended if needed by directly writing into the table before generating the roster. However, adding or deleting rows or columns here will alter and mess up the whole roster generation engine. 

Roster

Excel Shift Roster Generator can create the schedule for a single month or the whole year. We just need to input the starting month and year. It also allows to choose the starting shift number.

This version only creates weekly rotations. The code can be easily leverage to change the periodicity of rotations, or the rotation logic itself. In the current logic, Excel Shift Roster Generator simply copies the pre-defined shifts presented in the worksheet “Shifts” into the schedule day by day for the list of resources available. Initially, the first shift will correspond to the first resource, unless otherwise specified. Then, with each week’s rotation it just moves one shift ahead, so the first resource will have the second shift.

Click Here to download Excel Shift Roster Generator.