How to Create Shift Roster 24×7 with Excel Automation

Shift Roster is a useful Excel automation tool. It can be used for employee scheduling. With this tool, we can change and create new schedules in seconds. This is particularly useful when someone is working with a large number of employees and their schedules. In this article, we will show you how to create a Shift Roster 24×7 with Excel Automation.

This overview video shows how you can create a Shift Roster just by setting the First Shift for each employee.

Download Practice Workbook

You can download this practice workbook.

Steps to Create a Shift Roster 24×7 with Excel Automation

In the Excel worksheet, we can create a Shift Roster 24×7 with automation. And for that, we have to follow some steps. So, let’s see those steps and create the Shift Roster.

1st Step: Define Names for Dropdown List

We need to create dropdown boxes for 3 different items. One for selecting a Month. Another one is for selecting the Year, and the last one is for selecting the Shift legend.

We have taken five shifts from morning to night. M1, M2 represent morning shifts. A is for afternoon, and N1, N2 are for night shifts. And 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. Do the following tasks.

• Copy the range B5:B16.
• Then select Define Name from the Formulas

• A window will open where you have to define a name for range B5:B16. We named it Settings_Month.
• After that, press the OK

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

• And 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.

2nd Step: 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 dropdown list, we’re going to use the Data Validation tool of Excel.

This Shift Roster can also be used starting 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.

First, we will start with the month dropdown box.

• Select the merged cell J4.
• Then, select Data Validation from the Data

A window like this will open to import the data source.

• Select List from the Allow dropdown box.
• Then 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.

• First, select all the cells from C8:C17.
• Then import the defined name Settings_Shift_Legend through Data Validation as shown earlier.

This video shows how the dropdown boxes will work.

3rd Step: Insert Formula for Relevant Fields

For this template to work, we need to use some formulas. Let’s see them.

We want to start at the beginning of the month. We will use the DATEVALUE function as the formula.

• So, write 1 in the Month Start
• Then, write the following formula in the Start Date box and press Enter.

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

Doing so, the start date will fill in. You might have to change the format to the date.

Now, we have to use a formula for End Date. For this, we will use the EOMONTH function.

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

This formula gives End Date based on the Month Start and Start Date values.

After that, we have to fill in the dates from the beginning to the end.

Our starting date is 01/01/2022.

• So, write =E5 in the E7 cell and press Enter.

You will get “01” as the starting date.

• Now write the following formula in F6 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.

After that, we want to create weekdays. For that, we have to use the TEXT function.

• So write the following formula in E6 and press Enter.

This formula converts the dates into weekdays.

You can see that the formula gives a weekday.

• Now, hold and drag the E6 cell rightward to get all weekdays.

Thereby, we get all weekdays.

4th Step: Find Shift Roster for All Employees

Our template is almost ready. We will select First Shift and will use another formula to create Shift Roster. In this formula, we will use IF, OR, INDEX, MATCH, and COUNTA functions.

• First, select a First Shift.

• Then 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.

• After that, hold and drag the M1 cell rightward to get Shift Roster for the first employee.

• Make sure the Shift Roster for the first employee is selected.
• And hold and drag the entire selected range downwards to copy the formula from E8 to AI17.

• Now, 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.

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

Last Step: Shift Roster for Different Month

We 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.

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 Excel to generate automatic alerts or reminders for shift changes. For that 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 for any practical cases.

Conclusion

So, we have shown you the step-by-step procedure for creating a shift roster 24×7 with Excel automation. This roster can be useful if you want to manage employee shifts. We hope you find the content of this article useful. For more articles like this one, you can visit our website, ExcelDemy.com. If there are any questions, feel free to mention them in the comment box.