Sequence of weekends

The goal is to generate a series of sequential weekend days (Saturday and Sunday) with a formula. The start date is entered in cell B5. The number of dates to create (n) is entered in cell B8. If either of these two values are changed, a new list of weekend dates should be generated. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function. In older versions of Excel, you can also use the WORKDAY.INTL function with a more manual approach, as explained below.

WORKDAY.INTL function

The WORKDAY.INTL function takes a date and returns the next workday based on a given offset value provided as the days argument. WORKDAY.INTL will automatically exclude weekends and can optionally exclude dates that are holidays. In this example, we don't need to exclude holidays so we are only using the first 3 arguments in WORKDAY.INTL:

=WORKDAY.INTL(start_date,days,weekend)

• start_date - the date to start from
• days - the number of days to move forward or back
• weekend - a code to specify which days are weekends

One of the arguments provided to WORKDAY.INTL is called "weekend", and controls which days are considered non-working days. The weekend argument can be provided as a number linked to a preconfigured list, or as a 7-digit code that covers all seven days of the week, Monday through Saturday. In this code, a 1 indicates a weekend and a 0 indicates a workday. Each digit in the code represents a day of the week. The first digit represents Monday and the last digit represents Sunday. For example, to configure WORKDAY.INTL to get the next working day after a date in cell A1, where Saturday and Sunday are weekends, you can use a formula like this:

=WORKDAY.INTL(A1,1,"0000011") // weekends = sat, sun

To get the next working day when Friday, Saturday, and Sunday are weekend days, you can use a formula like this:

=WORKDAY.INTL(A1,1,"0000111") // weekends = fri, sat, sun

The screen below demonstrates how WORKDAY.INTL behaves with different codes. In all cases, the start date comes from column B and the weekend code comes from column C. The result in column F shows the next working day after the start date:

Notice as we add more weekend days (i.e. non-working days) the next working day gets pushed out. We use this behavior to list Saturdays or Sundays only in the formulas explained below.

Current version

In the current version of Excel (Excel 2019+), the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function. In the workbook shown above, the formula in cell D5 is:

=WORKDAY.INTL(B5-1,SEQUENCE(B8),"1111100")

The WORKDAY.INTL function takes a date and returns the next workday based on a given offset value. The inputs provided to WORKDAY.INTL are as follows:

• start_date - B5-1 (the day before the start date)
• days - array created by SEQUENCE (see below)
• weekend - "1111100" (code allowing Saturdays and Sundays only)
• holidays - omitted (not needed)

Working from the inside out, the SEQUENCE function is used to generate a sequential array of n numbers, where n comes from cell B8. With the number 12 in cell B8, SEQUENCE generates an array like this:

=SEQUENCE(B8)
=SEQUENCE(12)
={1;2;3;4;5;6;7;8;9;10;11;12}

The start_date is calculated by subtracting 1 from the date in B5. We do this because we want to force WORKDAY.INTL to evaluate the start date as well. If it is a Saturday or Sunday, it should be included in the list. Excel dates are just large serial numbers, so this operation evaluates like this:

=B5-1
=45170-1
=45169 // 31-Aug-2023

The number 45169 represents 31-Aug-2023, the day before the start date in B5.  Simplifying, we now have:

=WORKDAY.INTL(45169,{1;2;3;4;5;6;7;8;9;10;11;12},"1111100")

Since we are providing days as an array containing 1-12, we are asking WORKDAY.INTL for the next 12 work days after 31-Aug-2023 when weekend is given as "1111100". This is a special way of indicating which days of the week should be treated as weekend dates (i.e. non-working days), which are excluded by WORKDAY.INTL. There are 7 characters in the string, one for each day of the week starting on Monday. A "1" means the day is a non-working day (i.e. a "weekend") and should be excluded. A "0" means the day is a working day and should be allowed. With this configuration, WORKDAY.INTL, returns the next 12 Saturdays and Sundays after 31-Aug-2023, skipping the days Monday through Friday. For more details on WORKDAY.INTL, see How to use the WORKDAY.INTL function.

List weekends between dates

To adapt this formula to list the weekends between two dates (start and end), you can use a generic formula like this:

=LET(dates,SEQUENCE(end-start+1,1,start),FILTER(dates,WORKDAY.INTL(dates-1,1,"1111100")=dates))

See this page for a detailed explanation of how this approach works.

Legacy Excel

In older versions of Excel, there is no SEQUENCE function, so we don't have a simple way to request 12 dates at once. One simple solution is to set up the worksheet so that we can "drag copy" a formula that will return the next Saturday or Sunday after the "cell above". First, enter this formula in cell D5:

=WORKDAY.INTL(B5-1,1,"1111100")

Here we use the WORKDAY.INTL function to determine the next Saturday or Sunday after the start date with these inputs:

• start_date - B5-1 (the day before the start date)
• days - 1 (i.e. next date)
• weekend - "1111100" (code allowing Saturdays and Sundays only)

To get the start_date, we subtract 1 day from the date in B5 to force WORKDAY.INTL to evaluate the start date in B5 like other dates. If the date in cell B5 is a Saturday or Sunday, it will be returned by the formula above. Otherwise, the next Saturday or Sunday will be returned. As explained above, the weekend argument is given as the code "1111100" This tells WORKDAY.INTL to treat the weekdays Monday through Friday as "weekends" and Saturday and Sunday as workdays. The result is that WORKDAY.INTL will only return dates that are a Saturday or Sunday.

Next, in cell D6, enter the formula below and copy the formula down as needed:

=WORKDAY.INTL(D5,1,"1111100")

The inputs to WORKDAY.INTL are as follows:

• start_date - D5 (the "cell above")
• days - 1 (i.e. next date)
• weekend - "1111100" (code allowing Saturdays and Sundays only)

As the formula is copied down, it begins with the date in the "cell above" and returns the next Saturday or Sunday. Because the formula in cell D6 refers to the start date in cell B5, cell B5 is still operational and will drive all results. The downside of this approach is that the formula in D5 is different from the formulas in cell D6 and below, so you must take care to keep them separate.

Conclusion

The key to this formula is the WORKDAY.INTL function which can calculate the next working day, and can be customized with the "weekend" argument to treat Saturdays and Sundays as working days. In Excel 2019+, the SEQUENCE function can be used within WORKDAY.INTL to generate a dynamic array of weekend dates based on a start date and the number of weekends you want to list. In addition, you can combine WORKDAY.INTL with SEQUENCE and the FILTER function to list weekends between two given dates. In an older version of Excel without SEQUENCE, you can use a manual "drag copy" approach to get the same results.