How To Make A Rotating Schedule in Excel vs. OPTAS
Reading time 15 min.
Shift work isn't easy. Often, workers have to sacrifice normal social activities and even their health to attend non-typical shifts at night or on weekends. At the same time, many managers struggle to create rotating shift schedules and apply banked hour calculations due to the complexity of such a scheduling method. In this article, you'll learn the easiest way to make a rotating schedule in Excel vs OPTAS automated employee scheduling software.
A rotating shift schedule is a type of schedule based on employee rotation over a set period of time. This type of employee scheduling is most common in businesses that stay open outside traditional business hours or have to maintain operations 24/7. Employees work for a set number of days, take a few days off, and rotate between day, night, and swing shifts. This way, your business can operate without interruption, and your employees get a fair distribution of day and night shifts.
Rotating schedules are common in many types of industries
What are the main types of rotating shift schedules?
Rotating shift work isn't a one-size-fits-all kind of thing. As many industries and companies work in shifts, their rotation models change. For example, hospital workers work in shifts, and the hospital administration has to ensure there are enough employees to cover shifts 24/7/365. On the other hand, restaurant workers also work in shifts, but they rarely have to work through the entire night or very early mornings.
So, before you start making schedules, it's important to find a type of rotating shift pattern that works for your team and business type.
Below are the most common rotating shift schedule types.
The Pitman shift schedule
The Pitman schedule is based on four teams working 12-hour night and day shifts for 4 weeks and rotating every two weeks. In the first week, team 1 works day shifts for two days, rests for two days, and then works for three days. The second team follows the same pattern but on the night shifts.
The third and fourth teams start the first week with two days off, work for two days, and then have three days off. They also work night and day shifts. Then the first and second teams start their second week with 2 rest days, followed by 2 working days, and 3 days off again.
After two weeks, the workers rotate. So, if the first team was working day shifts, after the two weeks, they'll work night shifts and vice versa.
Here's what it’d look like:
D = Day shift
N = Night shift
O = Rest day
Week 1-2
Team 1: DDOODDD-OODDOOO
Team 2: NNOONNN-OONNOOO
Team 3: OODDOOO-DDOODDD
Team 4: OONNOOO-NNOONNN
Pitman's schedule created with OPTAS. Week 1-2
Week 3-4
Team 1: NNOONNN-OONNOOO
Team 2: DDOODDD-OODDOOO
Team 3: OONNOOO-NNOONNN
Team 4: OODDOOO-DDOODDD
Pitman's schedule created with OPTAS. Week 3-4
The Pitman schedule gives employees regular breaks and longer rotations to get used to their shifts.
The DuPont shift schedule
The DuPont schedule is one of the most hectic and hardest to manage. It's also based on 4 teams rotating between each other. Each team works either day or night shifts for 12 hours per shift, on a 4-week work cycle.
Here's an example of how the first team would rotate during the 4 weeks:
NNNNOOO
DDDONNN
OOODDDD
OOOOOOO
Dupont schedule created with OPTAS
What makes the DuPont schedule good is that each rotation period your teams get one week off work. But at the same time, the unstable schedule can confuse workers, cause more no-shows, and mess with their health and circadian rhythm. You might also notice that after 7 days off, workers take longer to ramp up and get back to their normal routines.
The 2-2-3 rotating shift schedule
The 2-2-3 rotating shift schedule also follows a 28-day cycle instead of the weekly rotation. In this schedule, several teams rotate between day and night shifts. On each shift, they work for 12 hours. The 2-2-3 schedule is usually used in manufacturing companies or hospitals where you continuously have to keep the work going.
Here's the 28-day pattern for team number one:
DDOONNN
OODDOON
NNOODDO
ONNNOOO
2-3-3 schedule created with OPTAS
This schedule gives more flexibility as workers have regular time off and everyone works both day and night shifts. However, frequent rotations between day and night shifts can mess with employee health, social activities, and sleep patterns.
Microsoft Office Excel is the first tool most accountants and schedule coordinators use to manage employee schedules and calculate salaries. If you use Excel, here's how to create a rotating schedule.
Microsoft Office Excel belongs to the Microsoft Office program suite. If you use a Windows computer, it usually already has the pack installed. If not, you'll need to buy the Microsoft Office Suite, which includes Excel. Follow the installation instructions to set up and launch the program.
You can also find free alternatives to Excel, such as Google Sheets on Google Workspace or LibreOffice. Both of these tools have a similar interface and most of the functionalities match.
Once you have Excel set up, go ahead and create a new document. In the newest versions of the software, you can choose from premade templates. Predesigned templates will already have the monthly layout applied, so you'll only need to fill in the shift information.
In empty cells, write down your preferred rotation pattern and employee data, such as the team numbers or employee names. To make everything clearer, you can mark each team's shifts in different colors.
Start by selecting the month you want to create a schedule for. Write down the dates to match the days of the week. Let's say the 1st of May 2024 is on Wednesday, set it correctly and follow the same day sequence for the rest of the month.
On the left side of the table, make two rows for night and day shifts. Now, manually input team colors to match the schedule pattern that you put before.
Here’s what it’s supposed to look like:
Rotating shift schedule in Excel
It's a simple way to schedule your team if each worker on your four teams follows the same schedule and rotates like that. However, things get more complicated when you have to schedule multiple workers on different shifts and rotate without a clear pattern.
Pros of scheduling employees with Excel
Easy access
Most computers already have Microsoft Office Excel or a similar spreadsheet program preinstalled.
Versatile
You can use Excel not only for employee scheduling but also for other calculations, formulas, data input, etc.
Affordable
Excel is often available on many computers. If you don't have the Microsoft Office Suite, the company offers deals for businesses. At the same time, when you buy the whole suite, you get other programs like Microsoft Office Word and PowerPoint.
Visibility
Excel schedules can be shared with employees via email or cloud storage, such as Google Drive or Dropbox.
Cons of Excel for rotating schedules
A lot of manual work
While you can use formulas to speed up your work, most data input is done manually. You have to manually enter employee data and fill out their shifts cell by cell.
Steep learning curve
Earning all the formulas and Excel tricks takes time. Even if you master it, your colleagues who contribute to your employee scheduling processes might not have the right skills or knowledge to use the program. This makes it more difficult to onboard new employees or delegate tasks.
Mistake risks
Making scheduling mistakes using Excel is far more common than you think, especially when it comes to scheduling multiple employees with various rotations. Manual information input, no auto compliance with your local labor laws, and unattended shift swaps can result in mistakes, missed shifts, and additional costs to your business.
Limited collaboration
Unlike cloud-based scheduling software, Excel files may not support real-time collaboration.
Limited automation
While Excel offers formulas and macros, it can't automatically schedule employees or track their attendance data. Likely, you'll need to do tasks like shift rotation, overtime calculation, reporting, and timesheet export manually.
Lack of tailored features
Microsoft Excel lacks features specifically designed for creating and managing rotating employee schedules. This means you can't automate scheduling, forecast staffing needs, generate attendance reports, or automatically import timesheets to your payroll or personnel management systems.
Risk of data loss
If Excel files are not regularly backed up or stored securely, there's a risk of data loss due to hardware failure, accidental deletion, or other accidents.
How to make a rotating schedule in OPTAS
OPTAS is an automated employee scheduling tool. That means you don’t need to create calendars, play with shifts, or manually create schedules because the software does that for you. It is specifically designed for the job of creating schedules and overseeing all related tasks, such as banked hour calculations, employee availability preferences, labor law compliance, and more.
Here’s a simplified step-by-step process of creating a schedule for your team with OPTAS.
After you subscribe to one of our plans and create an account, log into your OPTAS account. You'll see a window called 'schedule'. It's already synced with a calendar, so you won't need to enter days of the month.
You can import your employee data from other systems like payroll or personnel management programs, and all information will appear on the left sidebar of the schedule (see below). You can select which employees will appear in a selected month or accounting period.
Once you have imported the employee data, you'll need to select your schedule rotation type and enter employee preferences. Your staff can select vacation days, preferred shifts (night or day), and days off. When you approve their requests, you can simply click 'generate', and the system will automatically generate a schedule for the entire month or your selected accounting period.
With OPTAS, you can also make your schedules manually. On the row with employee names, you'll see how many hours they need to work that month. If you're using banked-hour accounting, it'll show the work quota for the accounting period.
By clicking on each day, you have options to select either a shift or a day off. For selecting a day off, you also have options such as a general day off, vacation day, family day, sick leave, or other reasons (see below).
How manual scheduling looks with OPTAS
In the window above, you can schedule shifts and select the time down to the minutes when the shifts start and end, and also add breaks. All this information automatically syncs with the cloud, and you can use it to generate various reports or export this data to your payroll systems to calculate salaries.
If you generate a schedule automatically, you don't need to worry about double booking employees or scheduling someone on the wrong shift because the system automatically schedules error-free and fills out all the shifts you need to meet your business demand.
When scheduling manually, the system notifies in case there's a mistake or compliance violation. For example, you agreed that employee will have a day off on August 15th because they need to go to a doctor. If you forget this information and schedule them on the 15th, the system will flag that it's not possible to schedule them that day.
Once you have your schedule ready, you can share it through the app or email. You can also give different access rights to your staff so they can access their personal schedules, request days off, or perform other related actions. In case of a schedule change, all relevant employees will be automatically notified.
Pros of OPTAS employee scheduling software
Saved time
We have calculated that our clients reduce scheduling time by 70% with OPTAS.
Minimized errors
Our solution automatically applies compliance rules and labor requirements. By eliminating manual data entry and calculations, you can completely eliminate scheduling mistakes such as double bookings, overstaffing, or missing shifts.
More flexibility
You can easily edit your schedule and change shifts as the month goes by. The schedule will then automatically update to ensure that no shifts are missed. You can make adjustments, swap shifts, or accommodate last-minute changes without disrupting the entire schedule.
Fairness
OPTAS schedules employees fairly. That means it distributes shifts evenly so everyone gets the same number of day, night, and weekend shifts. You can also schedule based on business occupancy. So, let's say Saturdays are busy, instead of one worker always working on Saturdays, OPTAS distributes the workload more evenly. This reduces the chances of favoritism and disputes.
Multiple scheduling options
You can also schedule based on employee availability, skill sets, labor laws, and business needs. This reduces scheduling conflicts and ensures adequate staffing during peak times.
Compliance
OPTAS is fully compliant with labor laws, union agreements, and your company policies. Our tool flags scheduling practices that may lead to overtime violations and ensures adequate rest periods between shifts.
Integration
OPTAS integrates with other workforce management systems, such as payroll, personnel management, and HR software. This way, you can share data more easily without worrying about manual work and data silos.
Analytics and reporting
OPTAS lets you automatically generate analytics and reports on labor costs, productivity, and schedule adherence.
We offer affordable plans, but it might be more expensive than Microsoft Excel or its alternatives.
Might fit bigger teams better
OPTAS works best for mid-sized and large teams. That’s when you can notice the most benefits of saved time.
The verdict
We hope that this article better explains how to create rotating schedules in Excel and OPTAS. Both of these tools have their pros and cons. While Microsoft Excel might be more widely available, OPTAS reduces manual work, automates your processes, and has all the necessary functionality for smooth schedule management.
If you're interested in seeing how much time you could save by automating your scheduling tasks, book a discovery call with us. During the call, we'll tell you more about our solution and help you decide if you need employee schedule automation software.
