Excel shift pattern
VBA roster function/formula

Create an Excel Formula with your shift schedule

This application automatically creates an Excel formula with your shift schedule, also called a "function".

The formula calculates the corresponding shift for each chosen date. With this function you can build your own schedules and applications in Excel.

For Office 2016 and Office 365 Excel:

  1. On the next page, select and copy the generated source code.
  2. If there is no "Developer" Tab on the ribbon, you must enable it because it doesn't appear by default.
    • On the File tab, choose the Options button.
    • Choose "Customize Ribbon" and under "Main tabs", check the box next to "Developer".
    • Choose the OK button to close the Options dialog box.
    • Return to your Excel worksheet.
  3. Select the "Developer" tab.
  4. Select "Visual Basic" in the menu bar to open the Visual Basic development window. Select "Insert" in the menu and then click on "Module".
  5. Paste the source code into the module. Then select "Close and return to Microsoft Excel" in the "File" menu.
  6. Back in Excel, type a date in one of the cells, eg 24/07/2017 in cell D1. Then choose another cell and type =SHIFT(D1). Select "Insert" in the "Formulas" menu. In the window that opens, choose "User defined" at "Or select a category". Select the "SHIFT" function and press OK. Now click the cell with the date and press OK. Please note that there is no output if the selected date is a rest day!
  7. You can use the SHIFT(date) function like any other function in Excel. If you want to create functions for several shifts(teams), download the formula for each team and change the text "SHIFT" to "SHIFTA", "SHIFTB", etc. in the formula. Use SHIFTA(date), SHIFTD(date)... instead of SHIFT(date) on your worksheets.
  8. Save your workbook as an "Excel Macro-Enabled Workbook".


Configure

All preset shift schedules are linked to a pattern. When you select a shift schedule from the list, the pattern is automatically filled in. In the same way, every team corresponds with a reference date.

Select a schedule and a team from the list and enter the desired year to create your shift calendar. You will find an overview of all schedules here.

Is your schedule not listed? You can create a schedule yourself. More information can be found in the faq.



If you'd like to send us feedback about the site, or if you've found something wrong then please contact us.