I was asked if it would be possible to convert my annual calendar template into an automated work rotation schedule. For an employee who is working a Rota consisting of number of days on followed by a number of days off. The result was the new template Work Rotation Schedule.
Rotating Shift Schedule
This template provides convenient way to display your work rotation schedule, using annual calendar template. Just enter year and start month, then use other inputs to define your shift schedule pattern. The workbook contains 3 separate worksheets, each providing a little different set of features.
In the first two worksheets (Rotation and Rotation Advanced). Rota schedule is define by entering numbers of days on Shift 1, follow by the number of days on Shift 2, follow by the number of days off. For simpler “days on/off” pattern, just leave “Shift 2” field blank.
Rotating Shift
In the Rotation String worksheet, you can define rotation as a repeating string such as “11xxx22xx333xx” which would mean that you work Shift 1 for 2 days. Follow by 3 days off, follow by working Shift 2 for 2 days, follow by 2 days off, follow by working Shift 3 for 3 days, follow by 2 days off.
Both the Rotation Advanced and Rotation String worksheets can use the new built-in function NETWORKDAYS.INTL(), available as of Excel 2010, to let you choose which days to make up the “weekend” (Sat/Sun, Mon/Tue, Sunday only, etc.) and to also account for holidays. In these worksheets, the rotation excludes both weekend and holidays, all of which we call “non-working days. “This means that if rotation calls for you to work 2 days starting on Friday, then you would work Friday and then Monday and skipping the non-working weekend days.
The workbook contains a “Holidays” worksheet where you can list days that is consider non-working days.
