Will calculate the number of people who are in at 7:00. =COUNTIFS(First person's start time: last person's start time, ' the relevant time') 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. Next, we calculate how many people are shifted at a particular time.The Excel function we will use is COUNTIFS. Automatic calculation of the number of people per hour 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. 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.

Working hours are calculated from the start and end time of the job.Įnd 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. Let's start by calculating the work hours on the far right. 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. Create a column for the total number of people 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.ģ. We'll design the whole thing later, and create a table of people and hours for the shift.

Let's create a 30 pixels wide by 30 pixels high. The first thing to do is to adjust the size of the vertical and horizontal cells in Excel.īy 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. The automatic calculation is based on the number of people in the shift and the number of hours worked that day.

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. If you want to make your own Excel shift chart instead of a template, here's how to make a shift chart. Fa-arrow-circle-rightDownload How to make a shift schedule in Excel

Employee monthly schedule template free