How to Create a Four-Week Work-From-Home Rotation Schedule in Excel
Question details
The user wants to build a monthly employee schedule in Excel that assigns rotating work-from-home weekdays, ensuring Fridays and weekends are excluded from the rotation.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing team schedules and assigning recurring work-from-home days dynamically for different employees.
- Observed behavior
- Needs a logical formula to calculate elapsed weeks and current weekdays to accurately output a marker (like 'W') for the assigned work-from-home day, while skipping specific days of the week.
Ensure you have a complete list of your employees, a predefined start date for the schedule, and a generated column of sequential dates for the calendar month.
Use MOD and WEEKDAY for a Continuous Rotation Pattern
This method is ideal for long-term schedules where the four-week rotation pattern continues seamlessly across different months without resetting at the start of a new month.
This formula uses a fixed start date to calculate the elapsed weeks, ensuring that the work-from-home assignment correctly shifts forward each week. It evaluates the sequence using the MOD function to handle the 4-week cycle.
Enter the designated start date for your rotation schedule into a reference cell, such as B1.
Create a sequential date series for the month or year starting in cell A5.
Enter the formula =IF(WEEKDAY($A$5#,2)=MOD(INT(($A$5#-$B$1+WEEKDAY($B$1))/7),4)+1,"W","") into the adjacent cell to generate the 'W' marker for the assigned work-from-home day.
To shift the first weekday from Monday to Tuesday for a specific employee, change the second argument in the WEEKDAY function from 2 to 3.

Calculate Weekly Shifts with INT and CHOOSE for Monthly Resets
Use this alternative method if your company's schedule specifically restarts the rotation cycle at the beginning of every calendar month.
Create Advanced Schedules in WPS Spreadsheet
WPS Office offers a robust Spreadsheet application that fully supports advanced logical and date formulas needed to build comprehensive rotation schedules for your team seamlessly.
- 1. Open a blank workbook: Launch WPS Spreadsheet and create a new blank workbook to serve as your schedule template.
- 2. Input the schedule grid: Set up your calendar dates in the first column and your employee names in the top row.
- 3. Insert the rotation formula: Input the continuous schedule formula into the cell immediately under the first employee's name.
- 4. Fill across the month: Select the cell and drag the fill handle down and across to automatically populate the entire month's rotation.

Frequently Asked Questions
How do I exclude public holidays from the rotation schedule?
You can wrap your scheduling formula in an IF statement that checks against a separate range of holiday dates using the COUNTIF function. If the date is found in your holiday list, have the formula return an empty string instead of the shift marker.
Why does the WEEKDAY function use 2 as the return type?
Setting the return type to 2 ensures that the week begins on Monday (represented by the number 1) and ends on Sunday (represented by 7), which is the standard structure for traditional business scheduling.
How do I change the formula to include Friday in the rotation?
If you no longer need to exclude Fridays from your work-from-home schedule, adjust the mathematical modulo (MOD) divisor to 5 instead of 4, allowing the formula to account for a full 5-day work week.




