Explanation
The goal is to generate a dynamic list of sequential working days with a formula.
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.
SEQUENCE function
Working from the inside out, let’s look first at theSEQUENCE function.
SEQUENCE is designed to generate numeric sequences in rows and/or columns.
For more details, seeHow to use the SEQUENCE function.
WORKDAY.INTL will automatically exclude weekends and can optionally exclude dates that are holidays.
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, seeHow 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.
you’re able to see this approach in the screen below.
If the date in D5 changes, all formulas will recalculate to return valid workdays.
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 theROW functionand 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.
Forweekend, we provide 1, which is a code that configures WORKDAY.INTL to exclude Saturdays and Sundays.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
Related videos
The SEQUENCE function
How to calculate due dates with WORKDAY
