Free Employee Shift Schedule Template for MS-Excel

You will find an Excel template for shift and work schedules.

If you manage your shift schedule in Excel, it's convenient because it allows you to calculate the working hours automatically.

If you are managing a large number of staff members in a handwritten shift schedule, it would be a hassle to edit it every time there is a correction, so take this opportunity to use an Excel template.

You can download monthly, weekly or daily shift schedule templates.

Shift schedule excel template

There are excel templates for a month, a week, and a day. You can use these templates as a base and modify them to make them easier to use.

Click to view a larger image.

Monthly shift schedule

Monthly shift schedule01

This is an Excel-generated gray shift chart template that allows you to manage the shifts of 10 staff members on a single A4 sheet of paper.
Enter a date in the "Period Start Date" field and the date and day of the week for the month will be automatically set.

To use the system, enter the start time in the "In" column and the end time in the "Out" column. There is no automatic calculation of working hours.

Monthly shift schedule03

This is a shift chart for managing staff shifts in a work category from A to F. The work categories from A to F are not used in any particular calculation, so you can change them as you see fit.
If you feel the table is too small, change the height of the rows to reduce the number of staff members.
Monthly shift schedule04

Template "shift04" keeps track of staff shifts from A to F. It is a shift chart template in almost the same format as Template "Shift03".

The difference is that it has a total column at the bottom, and it calculates the working hours automatically. You can see the number of people from A to F per day.

Monthly shift schedule07

This is a horizontal A4 shift chart template created in Excel. The day of the week and the date are set automatically when you enter a date in the "Date" field at the top of the template. Below the date there is a column for remarks.

Weekly shift schedule

Monthly shift schedule05

This is a horizontal A4 shift chart template created in Excel. The day of the week and the date are set automatically when you enter a date in the "Date" field at the top of the template. Below the date there is a column for remarks.

Daily shift schedule

Monthly shift schedule02

This A4 horizontal shift schedule template created in Excel allows you to keep track of 13 staff members' shift times per day. Just enter a start time and an end time, and the system will automatically calculate the working hours, the time chart and the number of people per hour.
The color of the chart is set in conditional format, so if you want to change the color of the chart, select each employee's cell at 7 o'clock and change it from "Conditional Format".
Monthly shift schedule06

This template that can allocate staff by managing the start time and end time of each staff member.
This template is almost the same format as Template "Shift02", but "Shift02" can be created by hourly increments, while "Shift06" can be created by 30-minute increments. When you enter the start and end time, the system will automatically calculate the number of people per hour and work hours. The chart is not created automatically, so you have to fill in the shift hours yourself.

How to make a shift schedule in Excel

If you want to make your own Excel shift chart instead of a template, here's how to make a shift chart.

There are various ways to make a shift chart, but in this article, I will try to create a shift chart like the one below, which automatically calculates the number of people in a shift for a day.

The automatic calculation is based on the number of people in the shift and the number of hours worked that day.

How to make a shift schedule

1. Adjust the size of the cell

The first thing to do is to adjust the size of the vertical and horizontal cells in Excel.

By default, the width is too long, so when you create a calendar for the front part of the shift table, you can't get enough dates.

Let's create a 30 pixels wide by 30 pixels high.

adjust the size of the vertical and horizontal cells

2. Create a table of names and calendars

We'll design the whole thing later, and create a table of people and hours for the shift.

In the sample, I made a table with the "type", "name", "task", "start time", "end time", and 7:00-11:00 pm to distinguish between full-time and part-time employees.

create a table of people and hours for the shift

3. Create a column for the total number of people

In this case, we're going to calculate the number of hours each person works and how many people come to work during those hours, so we're going to create a column to display the calculation results in the right-hand side and bottom of the Excel table we just created.

4. Automatic calculation of work hours

Let's start by calculating the work hours on the far right. Working hours are calculated from the start and end time of the job.

End time - Start time gives you the work hours, so enter =K3-I3 in the first work hours cell.If you don't do this, you will also get a "0" in the column where there is no person in charge, so don't display it.

=IF(K3="","",K3-I3)

I used the IF function in Excel to create a formula that says that if K3 doesn't have any data in it, it won't show anything in the cells.

If you copy the formula cell you created at the top to the bottom, the formula will be in all the cells of the working hours.

5. Automatic calculation of the number of people per hour

Next, we calculate how many people are shifted at a particular time.The Excel function we will use is COUNTIFS.

The COUNTIFS function calculates the number of people who match multiple search criteria within a specified range.In this case, we count whether the start and end times for each rep are within the relevant time.

=COUNTIFS(First person's start time: last person's start time, "<= the relevant time",First person's end time: last person's end time, "> the relevant time")

Calculate in

For example, if it's 7 o'clock

=COUNTIFS(I4:I16,"<=7",K4:K16,">7")

will calculate the number of people who are in at 7:00.
If you copy this cell to the right, you can get the number of people at each hour.

6. Align the design
Finally, align the design so that it is easy to see. Add a title, date and time field and color to the Excel and you're done. Inserting or deleting cells will not break the function you just created, so it's okay.

Summary

We've introduced you to how to make a shift chart and attendance chart, as well as an excel template.

I'm sure there are a lot of stores that create shift charts by hand, but once you create a template in Excel, you can automatically create dates and days of the week, making it easier to print out the shift chart every month.

If you want to revise or make a more user-friendly shift chart, you can easily do so once you learn some Excel, so try using the template to create your own shift chart.

Recommended