How to Create Shift Roster 24×7 with Excel Automation

Step 1 – Define Names for the Dropdown List

• Create dropdown boxes for 3 different items: Month, Year, and Shift.

Let’s say we have five shifts from morning to night. M1, M2 represent morning shifts. A is for afternoon, and N1, N2 are for night shifts. There are also two off days defined by O1 and O2. Each employee has to work one shift each day, except for off days. Let’s define the names for the months with the help of Name Manager.

• Copy the range B5:B16.
• Select Define Name from the Formulas tab.

• A window will open where you have to define a name for range B5:B16. We named it Settings_Month.
• Press OK.

• Similarly, define the name for the Year. We named it Settings_Year.

• Define the Shift Type elements in the same procedure. We named it Settings_Shift_Legend.

You can see all the names that have been defined by clicking the Name Manager option.

• If we click the Name Manager option, a window like this will open where you can see all the defined names.

Step 2 – Create Dropdown Lists with Data Validation

This is the outline of our Shift Roster. We will insert the Month and Year names in the dropdown boxes. Also, we need a dropdown box of Shift legends in each cell of the First Shift column. To create drop-down list, we’re going to use the Data Validation tool of Excel.

This Shift Roster can technically start anywhere in the month (e.g. 15 January – 14 February). You can set Month Start. This will automatically update the Start Date and End Date. We can set the First Shift. And the Roster will automatically update.

We have to create two dropdown boxes to specify which month of the year we are working with.

• Select the merged cell J4.
• Select Data Validation from the Data tab.

• A Data Validation will open to import the data source.
• Select List from the Allow dropdown box.
• Click on the Source box and press F3.

• A window named Paste Name will appear. You can see all the defined range names.
• Select Settings_Month and press OK.

• We can see Settings_Month in the Source box of the Data Validation window.
• Now press OK.

• Select the merged cell J4.
• You will see a dropdown arrow on the right side of the merged cell. You can select any month from there.

• Similarly, create a dropdown box in the merged cell M4 for Year.

Now, we have to create dropdown boxes for each cell in the First Shift column.

• Select all the cells from C8:C17.
• Import the defined name Settings_Shift_Legend through Data Validation as shown earlier.

Step 3 – Insert Formula for Relevant Fields

Let’s say we want to start at the beginning of the month:

• Write 1 in the Month Start.
• Write the following formula in the Start Date box and press Enter.

This formula takes values from three cells and gives values in date.

• The start date will fill in. You might have to change the format to a date.

• Copy the following formula to the End Date box and press Enter.

• Write =E5 in the E7 cell and press Enter. You will get “01” as the starting date.

• Write the following formula in F7 and press Enter.

This formula gives sequential dates up to the End Date.

• Then hold and drag the F7 cell rightward to get all the dates.

• By doing so, we have dates for the whole month.

• Write the following formula in E6 and press Enter.

This formula converts the dates into weekdays.

• Hold and drag the E6 cell rightward to get all weekdays.

• Thereby, we get all weekdays. Modify the cell formatting so the text is rotated (Home > Orientation > Rotate Text Up).

Step 4 – Find Shift Roster for All Employees

• Select a First Shift.

• Write the following formula in E8 and press Enter.

This formula checks the First Shift cell($C8) and the upper date cell(E$7) if they both are blank. When both of them are blank, the formula returns an empty string.

If not, it checks if the previous shift cell(D8) is empty. If D8 is not empty it finds the value of D8 in the “Setting_Shift_Legend” range and returns the next shift legend.

It returns the value of C8 if D8 is empty.

In short, this formula gives sequential Shifts for each employee.

This will give you M1.

• Hold and drag the first result cell to the right to get Shift Roster for the first employee.

• Then hold and drag the entire selected range downwards to copy the formula from E8 to AI17.

• For the second employee’s First Shift, select M2.

And the second row will be filled with Shift legends indicating the employee’s shift on each date.

• Select the First Shift for all other employees and the worksheet will automatically give their Shift Roster.

Step 5 – Shift Roster for Different Month

You can create a Shift Roster for different months by just changing the Month and Year.

• From the dropdown boxes, change the month to February and the Year to 2020.

And automatically, the spreadsheet will give the Shift Roster for February 2020.

This month has 29 days, and it has been modified accordingly.

Read More: How to Create Monthly Duty Roster Format in Excel

Frequently Asked Questions

1. What information should be included in a shift roster?

A shift roster can include the name of the employee, the date, the start and end time of the shift, the type of shift (e.g. morning, afternoon, night), and any notes or comments (e.g. overtime, training).

2. Can I use Excel to generate automatic alerts or reminders for shift changes?

Yes. You can use VBA code or conditional formatting. For example, you can assign a rule that highlights shifts that have been changed or send an email notification to affected employees.

3. Can I use Excel to track employee attendance or time off?

Yes, you can track employee attendance or time off by adding columns or sheets to the roster and you can use formulas or functions to calculate the total hours worked or missed. You can also use conditional formatting or data validation to indicate absences or holidays.

4. Can I use Excel to create a visual representation of the shift roster?

Yes, you can use Excel to create a visual representation of the shift roster with charts or graphs. For example, you can create a Gantt chart to show the duration and overlap of shifts or a pie chart to show the distribution of shifts by type.

5. How can I protect the shift roster from accidental or intentional changes?

You can protect the shift roster using Excel’s protection features such as password protection, worksheet protection, or workbook protection from accidental or intentional changes. You can also restrict specific cells or ranges from editing or formatting.

Things to Remember

• You should ensure proper formatting for the start and end dates.
• Mention all relevant information about your Shift Roster.
• You can use conditional formatting to highlight any shift that has special characteristics.
• You should check your roster properly before using it.

Download Practice Workbook

You can download this practice workbook.

Related Articles

• How to Create Weekly Duty Roster Format in Excel
• Weekly Meal Planner Template with Snacks
• How to Create Automatic Schedule Generator for Free in Excel
• How to Create an Attendance Roster in Excel

<< Go Back to Roaster Templates | Excel Templates