Calculate Rotating Days Off and Three-Day Weekends in Excel
Question details
Create a schedule of rotating weekday breaks, Sundays, and three-day weekends using spreadsheet formulas.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An employee has every Sunday off and an additional weekday off that rotates forward, with Saturday added after a Friday break and the following week skipped.
- Observed behavior
- The desired schedule lists the rotating days, extra Saturdays, and recurring Sundays, including the skipped week after a Friday-to-Sunday weekend.
Confirm the first rotating weekday off and that every Sunday is a regular day off. The example starts on July 2, 2019, and skips the additional weekday break in the week after a Friday break.
Build the rotating schedule with separate date columns
Use helper formulas to calculate rotating weekdays, extra Saturdays, and Sundays for the schedule shown in the example.
The rotating weekday normally advances by eight days. After a Friday, it advances by ten days to Monday of the following week, leaving the intervening week without an additional weekday break.
The columns are separate lists, so dates on the same row do not necessarily belong to the same week. Friday, Saturday, and Sunday form the three-day weekend.
In a blank Excel worksheet, enter Rotating Day in A1, Extra Saturday in B1, and Sundays in C1. Enter =DATE(2019,7,2) in A2, or replace the arguments with your first rotating weekday off.
In A3, enter =A2+8+IF(WEEKDAY(A2,2)=5,2,0). Select A3 and drag its fill handle down for the number of rotating dates you need.
In B2, enter =IF(WEEKDAY(A2,2)=5,A2+1,""). Drag the fill handle down alongside the dates in column A.
In C2, enter =A2+MOD(7-WEEKDAY(A2,2),7). In C3, enter =C2+7, then drag C3 down for as many Sundays as needed.
Select columns A through C and choose Home > Number Format > Short Date. Check that the rotating dates include July 26 and August 5, 2019, with July 27 in column B and Sundays listed in column C.
Create the rotating days-off schedule in WPS Spreadsheets
Use the same date and weekday formulas in WPS Spreadsheets to list rotating breaks, extra Saturdays, and recurring Sundays.
- 1. Start the schedule: Open WPS Spreadsheets and create a blank workbook. Enter Rotating Day, Extra Saturday, and Sundays in cells A1, B1, and C1.
- 2. Enter the first rotating day: In A2, enter =DATE(2019,7,2), or use your own first rotating weekday off.
- 3. Calculate rotating dates and Saturdays: In A3, enter =A2+8+IF(WEEKDAY(A2,2)=5,2,0). In B2, enter =IF(WEEKDAY(A2,2)=5,A2+1,""). Drag each formula down using its fill handle.
- 4. Calculate Sundays: Enter =A2+MOD(7-WEEKDAY(A2,2),7) in C2 and =C2+7 in C3. Drag C3 down to extend the Sunday list.
- 5. Format and save: Select columns A through C and use the Home tab's number format control to display dates. Choose File > Save As to save the schedule as an XLSX workbook.

Frequently Asked Questions
Why does the rotating-day formula add ten days after Friday?
The formula adds eight days normally and two extra days when WEEKDAY identifies a Friday. For July 26, 2019, that produces Monday, August 5, leaving the week of July 29 without an additional weekday break.
How do I include Saturday in a Friday-to-Sunday weekend?
Enter =IF(WEEKDAY(A2,2)=5,A2+1,"") in B2 and fill it down. It displays the following Saturday only when the date in column A is a Friday; the Sunday appears in the separate Sunday list.
Can I start the schedule on a different date?
Yes. Change the date in A2 to the first rotating weekday off, then check the calculated dates against your schedule. The formulas assume the same eight-day rotation and ten-day jump after Friday.
Why are the dates in the same row not always from the same week?
Columns A and B follow the rotating-day sequence, while column C lists Sundays independently. Fill each column far enough to cover the date range you need.




