How to Create Weekly Duty Roster Format in Excel

Microsoft Excel is a powerful software. We use Excel functions for our educational, business, and daily staff duty roster management. It is important to build a duty roster format and keep track of each employee’s productivity when it pertains to company management. With this in mind, we will start from scratch and show you step-by-step procedures of how to create weekly duty roster format in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Why Do We Build Duty Roster Format?

This staff duty roster format is a useful tool for managers to monitor the job that employees are doing. Whether you’re a hotel executive, an army camp manager, or a member of staff managing a family business camp, a duty roaster is a need. The various camp-related tasks of every team member, including a housekeeping maid, school instructor for a camping trip, auto duty 24-hour security operations, doctor aid, and more, can be defined, scheduled accurately, and divided using a roster template. All Roster Format members would then be aware of when the services are necessary on a yearly, weekly, or daily basis.

Step-by-Step Procedures to Create Weekly Duty Roster Format in Excel

The article includes a thorough schedule for the entire week that will track both the planned work and the work actually completed each day. Moreover, the weekly roster format contains employee details and schedule details. Furthermore, we can add as many employees as we wish on the Excel basic sheet. To create a weekly duty roster format in Excel, we use the VLOOKUP and SUM functions. To illustrate, we take a dataset that represents a roster format of a company.

Step 1: Insert Staff Details

The objective of the first step is to include the necessary employee data to get a clear view of the employees. We can include the following sub-headers.

• Insert 3 sub-headers namely: Name, Role, and Rate.
• Add necessary data under the sub-headers.

Step 2: Specify Shift Pattern

The second step aims to justify the shift pattern specifications. Here, we will include the sub-headers:

• Put 4 sub-headers in the Shift Pattern section and name them Shift, Start, End, and Hours.
• Further, fill them with correct information.

Step 3: Start Work Scheduling Using VLOOKUP Function

The goal of this step is to make a work schedule for each individual employee. However, we need to use the VLOOKUP function to import the data from the dataset. The VLOOKUP function looks for a value in a given range and returns the value in the present range or array. Moreover,  this is an adaptable template which means we can add an infinite number of employee data. To create the weekly duty roster format in Excel, follow the procedures.

• First, write the following VLOOKUP formula in cell C5 to import data from another worksheet.

• Afterward, press Enter or Tab keys.
• Thus, the Role of the employee appears as Engineering.

• Now, pull the AutoFill handle down to get other employee roles.

• Similarly, import the rates of the employee. The formula in D5 is,

• Pressing Enter key will get you the output.

• Again, drag the AutoFill Handle down to get the other employee rates.

• Meanwhile, we input the week’s starting date in cell F4.
• If your cell is not customized before, we will need to customize the date into a day format.

• To customize, firstly, right-click on F4 and a context menu will pop up.
• Locate the Format Cells option and click on it.

• Subsequently, the Format Cells menu slides into the display.
• There, select Number > Custom > dddd > OK options consecutively.
• Hence, the date is set to a day (dddd) format.

• After that, simply write the below code to get the other weekdays.

• Later, drag the formula cell right up to X4 to get all the weekdays.

• Then, let’s put the Start hours into the schedule. To do so, write the VLOOKUP function again in F7.

• Afterward, copy the formula and paste it under each Start header.

• Likewise, in cell G7, type:

• Next, copy the formula and again paste them under End sub-headers.

• Alternatively, write the following formula in cell H7,

• Similarly, copy and fill every Hour’s header by pasting it.
• Lastly, do this on all other weekdays except Sundays.
• Therefore, we get a duty roster template format of our own.

Read More: How to Create Automatic Schedule Generator for Free in Excel

Step 4: Use SUM Function to Calculate Weekly Shift Hours and Pays

Previously, we built a roster model using the Excel function and other inputs. In this step, we will calculate the total shift hours and employee payments over the week. We will thoroughly use the SUM function to calculate the totals. As we know, this function determines the summation of a given array and returns an integer. Let’s implement the procedure following these easy steps.

• To begin with, type the formula in cell AB5,

• By tapping the Enter key we get the sum of the mentioned cell values.

• Later, drag the cell down to AutoFill.

• Alternatively, to find the total pays we write a formula in AC5,

• Pressing the Tab button will give you the multiplied output of D5 and AB5.

• Finally, hold the AutoFill tool and drag it down to obtain the total pay of each staff.

Step 5: Apply Conditional Formatting to Set up Duty Roster

In the last step, we will use the Conditional Formatting feature of Excel to add the final touch to our roster format. To customize the cell color of the shifts in our template, follow the below procedure.

• First, select the cell range B4:Z13.
• After that, from your Home tab, go to,

Home → Styles → Conditional Formatting → Highlight Cells Rules → Text that Contains

• Here, type Day Shift and select Green fill from the box.
• Hit the OK button to add color to the text.

• Similarly, for the Afternoon Shift add Yellow Fill in the same box.

• Add a different color for the Night Shift in the same way.
• Thus, we complete our weekly duty roster format.

Conclusion

In conclusion, we have discussed some easy steps to create a weekly duty roster in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.

Related Articles

• How to Create Monthly Duty Roster Format in Excel
• Create an Attendance Roster in Excel (with Easy Steps)