## Summary

To create a list of workdays that exclude weekends and holidays, you can use a formula based on the SEQUENCE function inside the WORKDAY.INTL function. In the example shown, the formula in D5 is:

``````=WORKDAY.INTL(B5-1,SEQUENCE(12),1,B8:B11)
``````

The result is a series of 12 dates, beginning on September 1, 2023, and ending on September 19, 2023. Notice that weekends and holidays are not included in the result.

Notes: (1) If you don't need to exclude weekends or holidays, see this formula. (2) In older versions of Excel without the SEQUENCE function, you can use a more manual approach as explained below. (3) To generate a list of working days between two dates, see this formula.

## Generic formula

``=WORKDAY.INTL(A1-1,SEQUENCE(n),weekend,holidays)``

## Explanation

The goal is to generate a dynamic list of sequential working days with a formula. The start date should be a variable input, and weekends and holidays should be automatically excluded from the output. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function like this:

``````=WORKDAY.INTL(B5-1,SEQUENCE(12),1,B8:B11)
``````

The result is a list of 12 working days, starting on September 1, 2023, and ending on September 19, 2023. Weekends and holidays are omitted from the result. Note that the number of dates to return is hardcoded into the formula as 12. However, this value could easily be exposed on the worksheet as another variable.

In older versions of Excel without the SEQUENCE function, you can use a more manual approach, which is also explained below.

### SEQUENCE function

Working from the inside out, let's look first at the SEQUENCE function. SEQUENCE is designed to generate numeric sequences in rows and/or columns. The generic syntax for SEQUENCE looks like this:

``=SEQUENCE(rows,[columns],[start],[step])``

In this example, the goal is to generate a sequence of 12 workdays that exclude weekends and holidays. The hard part of this problem is done by the WORKDAY.INTL function (see below) which is designed for this purpose. However, we need a way to ask  WORKDAY.INTL to return 12 workdays, and this is where SEQUENCE comes into the picture. We use SEQUENCE inside WORKDAY.INTL to create a value for the days argument like this:

``=SEQUENCE(12)``

With the number 12 given for the rows argument, SEQUENCE returns an array that contains 12 numbers starting with 1 and ending with 12:

``{1;2;3;4;5;6;7;8;9;10;11;12}``

The other arguments not provided to SEQUENCE (columns, start, and step) all default to 1.

For more details, see How to use the SEQUENCE function.

### 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. The generic syntax for WORKDAY.INTL looks like this:

``=WORKDAY.INTL(start_date,days,[weekend],[holidays])``

The arguments inside WORKDAY.INTL have the following purpose:

• start_date - the date to start from
• days - the number of days to move forward or back
• weekend - the weekend scheme to use
• holidays - dates to be excluded as holidays

The screen below shows the basic operation of WORKDAY.INTL:

In the worksheet shown at the top, we use WORKDAY.INTL with the SEQUENCE function like this:

``````=WORKDAY.INTL(B5-1,SEQUENCE(12),1,B8:B11)
``````

The inputs provided to WORKDAY.INTL are as follows:

• start_date - B5-1 (the day before the start date)
• days - array created by SEQUENCE
• weekend - 1 (Saturdays and Sundays)
• holidays - B8:B11 (dates that are holidays)

With the configuration above, WORKDAY.INTL starts on the day before the start date and calculates 12 workdays using the numbers returned by SEQUENCE as the days argument. After SEQUENCE runs, the formula looks like this:

``=WORKDAY.INTL(B5-1,{1;2;3;4;5;6;7;8;9;10;11;12},1,B8:B11)``

You can see now that we are asking WORKDAY.INTL for twelve results: the workday 1 day after the start date, the workday 2 days after the start date, the workday 3 days after the start date, and so on. The reason we start one day before the start date is to force WORKDAY.INTL to evaluate the start date as a potential weekend or holiday so that it can be excluded if necessary.

Notes: (1) weekend is optional and will default to 1 to exclude Saturdays and Sundays. (2) Holidays is an optional argument and can be omitted. (3) For more details on WORKDAY.INTL, see How to use the WORKDAY.INTL function.

## Legacy Excel

In older versions of Excel, there is no SEQUENCE function. This means we don't have a simple way to calculate 12 workdays all at once. However, one simple solution is to use the WORKDAY.INTL and "drag copy" the formula down as needed. You can see this approach in the screen below. The formula in D6 looks like this:

``=WORKDAY.INTL(D5,1,1,\$B\$8:\$B\$11)``

The inputs to WORKDAY.INTL are as follows:

• start_date - D5 (the "cell above")
• days - 1 (i.e. next workday)
• weekend - 1 (Saturdays and Sundays)
• holidays - \$B\$8:\$B\$11 (holidays as an absolute reference)

As the formula is copied down, it simply calculates the "next workday" after the date in the row above. If the date in D5 changes, all formulas will recalculate to return valid workdays.

Notes: (1) The formula in D5 is simply =B5 so that the start date in cell B5 is still functional. (2) One limitation of this formula is that it does not evaluate the start date as a potential non-working day.

### More complete option

It is also possible to generate workdays with a more complex formula that uses the ROW function to work out the number of days to give WORKDAY.INTL. This allows the formula to evaluate the start date and makes it possible to copy the same formula into D5:D16. In the screen below, the formula in cell D5 is:

``=WORKDAY.INTL(\$B\$5-1,ROW()-4,1,\$B\$8:\$B\$11)``

The inputs provided to WORKDAY.INTL are as follows:

• start_date - \$B\$5-1 (the day before the start date)