Calculation Issues

Calculate Rotating Days Off and Three-Day Weekends in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Create a schedule of rotating weekday breaks, Sundays, and three-day weekends using spreadsheet formulas.

Calculate Rotating Days Off and Three-Day Weekends in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Add headings and a start date

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.

2
Calculate the next rotating day

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.

3
Add Saturdays after Fridays

In B2, enter =IF(WEEKDAY(A2,2)=5,A2+1,""). Drag the fill handle down alongside the dates in column A.

4
List recurring Sundays

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.

5
Format and check the dates

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.

Check your rotation rules: These formulas match the supplied example. If your first rotating day or skipped-week rule differs, check the resulting dates against the published schedule and adjust the formulas as needed.
Calculate schedules in WPS Spreadsheets

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. 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. 2. Enter the first rotating day: In A2, enter =DATE(2019,7,2), or use your own first rotating weekday off.
  3. 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. 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. 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.
Calculate Friday-to-Sunday weekends and the following skipped week.Open and save Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and fill handles.Create and edit spreadsheets for free.
microsoft office alternative - wps office

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.