Shift Scheduler

Shift Scheduler Download Help License +bizpep.com

Shift Scheduler will build a roster structure to your specifications, allocate staff automatically, and generate a printable roster, all at the click of a button. It is compact and user friendly allowing you to add staff and shifts (maximum 150) with ease. Default values for continuous shifts mean that the only shift data required is the start date/time and length for the first shift, everything else is automated. For non-continuous or varying shifts you have the capacity to overwrite default data and structure shift settings to suit your specific requirements.

Constraints you can apply when allocating staff include the maximum hours a staff member can work within the roster period, the maximum continuous work hours, the minimum break between shifts, and the maximum number of days in a row that can be worked. You can also set a Yes or No work status to define a must work or must not work requirement. Your roster is printed using standard Excel commands or using the Format Print function to build a compact and easy to read roster schedule table. The Next Roster function allows you to roll roster details into the next roster period to quickly build consecutive rosters.

Download Shift Scheduler

Download Shift Scheduler - Compressed Zip file
shiftscheduler.zip

or

Download Shift Scheduler - .xlsm file
shiftscheduler.xlsm

This application is a macro-enabled spreadsheet developed in Microsoft Excel and will only function when opened using a spreadsheet program with macros enabled. You can download Shift Scheduler free of charge for evaluation. In evaluation mode some functionality is restricted. To license your application and fully enable all functions requires the purchase of an application license. If you have not already please consider buying a license to fully enable your application.

If you download the Compressed Zip file unzip/uncompress the downloaded file and save the content .xlsm application file. If you download the .xlsm file simply save the .xlsm file. Open the .xlsm file and follow the instructions. Macros must be enabled for this application to function. If you cannot access input or command buttons do not operate macros are not functioning. In this case please refer to your spreadsheet help to enable macros. This application does not access or alter your system or system files in any way. To uninstall simply delete the saved files.

Application suitability must be independently assessed and use indicates acceptance of any and all associated risk.

Help Shift Scheduler

Specific item help is provided within the application when you mouseover the red cell markers.

Summary

1. Click Add Staff and input the number of staff you wish to add to your roster. Initially the roster has 1 staff member. When you click OK these will be added as rows. In the blue cells of the staff rows input the staff names.
2. Click Add Shifts and input the number of shifts you wish to add to your roster. Initially the roster has 5 shifts. When you click OK these will be added as columns.
3. Set Shift Details in the blue cells of the first shift column by inputting the Start Date/Time, the Length and the Shift Requirement (i.e the number of staff required to work this shift). Shift details for following shifts are set to automatically follow on from the first shift. If your shifts are not continuous you can adjust the details in the yellow cells. Shifts should be set up in their Start order. To force a staff member to work a specific shift input a "Y" in the staff/shift cell, to force a must not work input a "N" in the staff shift cell. You can also input a Title for your roster in the blue cell above the shift columns.
4. Set Roster Constraints in the blue cells of the top table. These are the limits applied in the roster. They include the Maximum Hours staff can work during the roster (the roster is the period covered by the shifts); the Maximum Continuous Hours staff can work without a break (i.e the maximum length of a work period); the Minimum Break staff must have between work periods; and the Maximum Number of Days in a Row that can be worked. The Build Attempts is the number of times a roster solution will be searched for, in most cases this should be left as 5.
5. Click Build Roster and the Shift Scheduler applies a random process to build a roster inline with your input variables contained in the blue cells. The result appears as YES (work shift) or NO (do not work shift) in the staff/shift table.
6. Click Format Print and the completed roster is formatted by week on the Format Print sheet and can be printed using standard Excel print functions.
7. Next Roster clears shift allocations but maintains the roster structure and uses current last shift details as input for the first shift of the next roster. This allows you to build a roster for the next roster period considering the details from the current period.

Add Staff / Add Shifts

You can add or delete Shifts and Staff by selecting the required staff row or shift column to delete or the required insert row/column to add and then clicking the applicable Add Shift, Delete Shift, Add Staff, or Delete Staff button. The first staff row and the first 5 shifts can not be deleted. When you Add you will be asked how many shifts or staff to add, this is in addition to existing shifts and staff. When you select a cell within the roster matrix staff are added above it and shifts to the left. If there is no selection in the roster matrix staff are added to the top and shifts at the end of the roster matrix. The maximum number of shifts is 150.

Shift Details

In the blue cells of the first shift column input the Start Date/Time, the Length and the Shift Requirement (i.e the number of staff required to work this shift) for the first shift. The Start Time is in 24 hour format (hh:mm), the shift Length is in hours decimal format (1.5 is one and a half hours). Initial formula set the following shift details to follow on from the first shift with the same Length and Shift Requirement. If your shifts are not continuous or vary you can adjust the details in the yellow cells for each individual shift. Shifts should be set up in their Start order. Input a Title for your roster in the blue cell above the shift columns.

You can set a Yes or No work requirement by inputting a "Y" or "N" to mark a matrix cell before you Build Roster. For example if your first staff member cannot work your first shift input a "N" in the cell where the first staff row and the first shift column intersect (D24). If the first staff must work the first shift input a "Y". Marked cells only operate for 1 Build Roster and are then cleared to allow a new roster.

Roster Constraints

These are the limits applied in the roster. Input the limit values in the blue cells of the top table.

Maximum number of Hours staff members can work during the roster period. You can use Maximum hours to distribute shifts between staff. For example if you have 4 shifts of 8 hours with a shift requirement of 1 and 4 staff when you set Maximum Hours to 8 Build Roster will attempt to allocate 1 shift to each staff member. If there is no maximum then input a number greater than the total roster period.

Maximum number of Continuous (without a break) Hours staff members can work during the roster period. For this to apply you must also input Minimum Break Hours greater than 0. If there is no maximum then input a number greater than the total roster period.

Minimum Break in Hours staff members must have after completing a shift or group of shifts before they can work another shift. If there is no minimum then input 0. A staff member can work consecutive shifts without a break as long as the Maximum Continuous Hours limit is not exceeded or Minimum Break Hour is 0. Once a break starts a staff member cannot work another shift until the Minimum Break duration has passed.

Maximum Number of Days a staff member can work in a row. This the maximum number of days that can be worked before a full days break must be taken. If a staff member works any part of a day it is counted as a worked day.

Number of Build Attempts that should be made. Initially start with a Build Attempts value of 5 if a roster solution is not found increase the number of attempts. A random process is applied to solve the roster and it can follow a dead end path. The Build Attempts value allows it to exit the dead end and retry. When you Build Roster the Build Attempt number is displayed as the first character in the status line. As roster complexity increases more Build Attempts are required, this can increase the build time.

Build Roster

After you have added the required details for your roster period click Build Roster to identify a staff/shift allocation that meets your requirements. Staff members required to work a shift will be identified with "YES" in the shift column. Requirements that cannot be fulfilled are shaded tan. Program status is displayed at bottom of the window. To Exit Build Roster before it is complete press the Esc (escape) key. If you Exit in this way you should then Clear Roster to clear roster calculations. Build duration depends on roster complexity and the random process applied, the range is usually 1 to 10 minutes. Build Attempts is the number of times a solve will be attempted within one build roster, default value is 5. Complex rosters require more build attempts. Regardless of the build attempts setting the Build Roster will end as soon as a solution is found. Reset Roster removes all added shifts and staff and resets the roster to initial default values.

To maintain Staff and Shift headings while scrolling through your roster you can Freeze Panes. Select the cell below and to the left of the cells you wish to keep in view then from the Excel Toolbar go Window, Freeze Panes.

Format Print

You can Print your roster using the standard Excel commands to print sections of the Input Sheet as displayed. To set the print area from the Excel Toolbar go View, Page Break Preview or use File, Print Area, Set Print Area. Format Print transfers Staff and Shift details to the Format Print sheet. Format Print builds a table with Staff Names in the rows and Day Names in the columns 7 columns wide (1 week). The title of each shift a staff member is to work is displayed in the Staff/Day matrix. The Format Print sheet is only updated with the current roster details when the Format Print command button is used. The Format Print sheet is not protected and all formatting can be adjusted as required. You can also copy and save the Format Print sheet in a new Excel workbook. To do this open a new workbook, highlight and copy the required area on the Format Print sheet, and then paste to the new workbook.

Next Roster

Next Roster allows you to roll your roster details into the next period. It applies current last shift details as input for the next roster period except cumulative hours worked which are reset to 0, maintains existing staff and shift inputs, and clears shift allocations. Start Date and Time of the first shift are set as the end of the last shift in the current roster. This allows you to build a roster for the next period considering the current roster.

Show Detail displays any roster Initiation set by the Next Roster function and the Calculation Rows for each staff line. Examining the these details will assist understanding of roster formulation. Hide Details hides Initiation and Calculation details. To clear the Initiation settings use Clear Next, this only clears the Next Roster function Initiation settings on the Input sheet not the staff or shift details.

License Shift Scheduler

Please consider supporting application development. Licensing your application will fully enable all functions.

License your application now
  $69.00 USD per year

Secure online transaction processing is provided via PayPal. License details are sent by email as soon as your transaction is processed and will fully enable your application.

Application suitability must be independently assessed and use indicates acceptance of any and all associated risk.