Explanation
The goal is to list the working days between a start date and an end date.
In addition, we need an option to exclude a list of given holidays.
Note: In the workbook shown, all dates use thecustom number format"ddd d-mmm-yyyy".
SEQUENCE is designed to generate numeric sequences in rows and/or columns.
Inside the main formula, the LET function defines the array above as the variabledates.
Method 1
The first method relies on theWORKDAY.INTL functionto test dates as working days.
This approach builds on theDate is workday formula here.
The WORKDAY.INTL function is an upgraded version of the older WORKDAY function.
WORKDAY.INTL willautomaticallyexclude weekends (Saturday and Sunday) and can optionally exclude dates that are holidays.
That is the basic operation of WORKDAY.INTL.
For more details, seeHow to use the WORKDAY.INTL function.
It builds on theDate is workday formula here.
The WORKDAY.INTL function does not allow you to test a date with a “zero offset”.
In other words, you might test just a single date by providing the date with 0 for days.
Then you’ve got the option to compare the result to the day you want to test.
If they are the same, you know you have a workday.
If they are different, you know you have a non-working day.
Then we ask WORKDAY.INTL for the “next workday” using the altered values.
Finally, we compare the results from WORKDAY.INTL with the original dates.
Finally, the FILTER function uses this array to filter out non-working days.
Exclude Holidays
The formula above can be easily extended to exclude holidays as well.
For example, you could check and exclude dates using more than one list of holidays.
This array is then assigned to the variabledatesby the LET function.
WEEKDAY returns a number for each day of the week.
Providing 2 forreturn_typetells WEEKDAY to return numbers that correspond to aMonday-basedweek.
In this scheme, Monday is 1, Tuesday is 2, Wednesday is 3, etc.
After FILTER runs, the result is the eleven workdays seen in the range D5:D15.
Note that this version of the formuladoes notexclude holidays.
See below for an option that does.
If XMATCH finds a match (i.e.
the date is a holiday) it will return a number representing the position of the match.
Consequently, we use theISNA functionto test for an #N/A error.
IF ISNA returns TRUE, it means the date is not a holiday.
If ISNA returns FALSE, the date is a holiday.
Notice the WEEKDAY expression and the ISNA function are joined with a multiplicationoperator(*).
This is an example ofBoolean algebra.
Effectively, it joins the two expressions with AND logic, so both must be true.
The 0s represent dates that are a Saturday a Sunday or a holiday.
Only the dates associated with 1s make it through FILTER.
This makes the remaining code below more generic and easier to read.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable.
you could use the WEEKDAY function inside other formulas to check the day of week.
