How to make a rotating shift schedule in Excel

A shift schedule is a must in any company where the staff work on a rotation basis. Not only is it a handy human resources management and organisation tool, it also means employees always know when they have to go to work. 

A well organised shift schedule is a way to avoid chaotic processes and keep your company in order. We explain how to set one up in Microsoft Excel.

What is a work shift schedule?

A work shift schedule is a table that provides a visual overview of employees’ scheduled hours in the company. You can set up a monthly, quarterly or even annual schedule so workers know their shifts in advance. 

The aim of the schedule is to create a simple and efficient rotating production system and ensure the hours assigned are covered. 

Therefore, having a clear and well organised table is essential for companies where employees follow this system.

5 steps to create a rotating shift schedule in Excel 

A work shift schedule will look different depending on many factors: the number of employees included (there are individual schedules) or the period of time they cover, among other things. However, the simplest and most common way is to do a monthly table for the whole organisation.

Let’s explain how to set it up:

Step 1: open an Excel document

The first step is simple: you need to create a document in Excel and give it a name that you will be able to find easily. If you use a Cloud system like Dropbox or Google Drive, you will be able to share it with the staff easily. Likewise, HR software will let you store the template in the documents section for each employee so they can access their hours automatically. 

Step 2: create the calendar for the month

Starting in the second column, create the calendar for the month. Enter the day of the month in each cell including weekends. In the first row you can also indicate the days of the week. 

Lastly, we recommend you shadow the columns corresponding to the weekends to identify them easily. 

Step 3: enter the employee names

In the first column, starting at the row just below the days, enter the names of all the employees in the company. This is where you will indicate which shifts the worker has that week. 

Step 4: create a code

To mark if the employee is on the morning, afternoon or night shift, you can create a simple code, in which M, for example, means morning, A is afternoon and N is the night shift. 

To complete the schedule, you just need to enter the letter in the relevant cell according to the shift the employee is working.  

Step 5: Create new sheets for each month 

We recommend you do a monthly schedule and create new sheets as you go along. That way you have everything in one archive. 

And that’s all there is to it. An Excel schedule to organise the company’s staff shifts. Remember: 

• Respect employee rest periods.
• Don’t allocate night shifts for more than two consecutive weeks (unless the employee requests it).
• Ensure there are 12 hours between the end of one working day and the start of another.
• Communicate shifts in good time to employees so they can get themselves organised.