How to Create a Repeating Work and Rest Shift Pattern in Microsoft Project
Question details
The user needs to set up a rotating work and rest schedule, such as an 11-days-on and 3-days-off cycle, in Microsoft Project.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- Modeling complex, repeating work and rest shift schedules for resources or project timelines.
- Observed behavior
- The user wants to successfully apply a custom calendar or VBA macro to automate a 14-day rotating cycle across the project timeline.
Identify your exact work and rest days to determine your total cycle length (e.g., 11 working days + 3 days off = 14-day cycle) before setting up the calendar or macro.
Set Up a Custom Base Calendar for the Shift Pattern
Create a custom Project calendar with required working hours and nonworking days to repeat the cycle manually across your timeline.
For a standard 14-day cycle (11 days on, 3 days off), you can define a custom calendar and apply it to your project or specific resources. This avoids the need for complex VBA coding if the cycle is straightforward.
Navigate to the 'Project' tab on the ribbon and click on 'Change Working Time'.
Click 'Create New Calendar', name it (e.g., '11 On 3 Off Shift'), and choose to make a copy of the Standard calendar.
Go to the 'Work Weeks' tab, click 'Details', and manually set the specific working times for the 11 working days and mark the 3 rest days as nonworking time.
Assign this new custom calendar to the relevant resources by changing their 'Base Calendar' in the Resource Sheet.
Automate Shift Cycles Using a VBA Macro
Use the VBA DateAdd function to calculate and apply complex repeating shift intervals programmatically.
Manage Schedules Easily with WPS Office
While Microsoft Project requires complex custom calendars and VBA coding for shift patterns, WPS Spreadsheet offers intuitive templates and formulas to manage team schedules, rosters, and timelines without any programming. It is a highly compatible, free alternative to Microsoft Office.
- 1. Download WPS Office: Install the free WPS Office suite from the official website.
- 2. Open a New Spreadsheet: Launch WPS Spreadsheet to start building your customized work schedule.
- 3. Use Schedule Templates: Click on 'Templates' to find pre-built work shift and project timeline templates to save time.
Frequently Asked Questions
How do I assign a specific shift calendar to a single resource in MS Project?
Open the Resource Sheet view, double-click the specific resource to open the Resource Information dialog, and select your newly created custom shift calendar from the Base Calendar dropdown menu.
Why does the VBA DateAdd function use 321 for a 14-day shift cycle?
The DateAdd function in this context calculates intervals by hours based on a 24-hour clock. For a 14-day cycle starting at 8:00 AM and finishing at 5:00 PM (17:00), the duration spans 13 full days (13 x 24 = 312 hours) plus 9 hours on the final day, totaling 321 hours.
Can I model an 11-on and 3-off schedule without using macros?
Yes, you can manually configure a custom calendar by editing the Work Weeks details in 'Change Working Time'. You specify exactly which days in the recurring 14-day period are working days and which are nonworking days.
