Sequence of workdays

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)
• days - ROW()-4 (we subtract 4 to start with 1)
• weekend - 1 (Saturdays and Sundays)
• holidays - $B$8:$B$11 (dates that are holidays)

We don't have the SEQUENCE function to generate a value for days, but this approach mimics the same behavior. As before, we first subtract 1 day from the starting date in cell B5. We do this to force WORKDAY.INTL to evaluate the start date as a potential weekend or holiday. To create a value for days, we use the ROW function and subtract 4. We subtract 4 because the formula is entered in row 5 and we want to begin with 1. You will need to adjust this value if the formula is entered in a different row. As the formula is copied down, this value will increment.

For weekend, we provide 1, which is a code that configures WORKDAY.INTL to exclude Saturdays and Sundays. For holidays, we provide B8:B11, a range that contains dates that should be treated as non-working days. Notice that $B$5 and $B$8:$B$11 are entered as absolute references because we don't want these values to change as the formula is copied. As the formula is copied down, the value returned by ROW() will increase and each subsequent formula will display the next workday starting from the date in the cell above.