WORKDAY.INTL does not block out holidays

[shawnbull]

I am using the following:-=WORKDAY.INTL(Z5,0,1,'date calcs'!$F$5:$F$19) to set up a list of date that correspond to dates from
Mon to Fri, but should exclude public holidays.
The Z column captures all the dates in sequence that follows Mon to Friday based on the weekday formula.
My understanding is that public holidays should not show if the date falls on a public holiday, yet 25 Dec and 26 Dec both
public holidays in RSA show up.
The "date calcs'!$F$5:$F$19 is the worksheet reference to the public holidays.

Can anyone assist as regards what the error may be

or provide a solution where the month and year is derived from the tab name eg DEC 2024
and incorporated into a date(B1,A1,C1) function where B1 is the derived year, A1 is the derived month and C1 is the 1st weekday of the month.
The rows run in sequence from the 1st weekday date of the month to the last weekday date of the month, but should exclude
the public holidays
So Dec 2024 should look something like this:-
02/12/2024 (falls on a Monday)
03/12/2024
04/12/2024
05/12/2024
06/12/2024 (Falls on a Friday)
09/12/2024 (skips weekend to next Monday)
etc to week of Christmas that should look like this
23/12/2024 (the Monday)
24/12/2024
27/12/2024 (skips the public holidays)
30/12/2024 (skips the weekend)
31/12/2024

 

[Dave Patton]

Did you use a named range for the Holidays list?
Did you check what range the named list refers to?
If you still wish to use the sheet name for the relevant month, I show in the post below the formulas without the Lambda.

Dates andTime 2024.xlsm
	M	N	O
1	01-Dec-23		Workdays
2			Fri 01-Dec-23
3			Mon 04-Dec-23
4			Tue 05-Dec-23
5			Wed 06-Dec-23
6			Thu 07-Dec-23
7			Fri 08-Dec-23
8			Mon 11-Dec-23
9			Tue 12-Dec-23
10			Wed 13-Dec-23
11			Thu 14-Dec-23
12			Fri 15-Dec-23
13			Mon 18-Dec-23
14			Tue 19-Dec-23
15			Wed 20-Dec-23
16			Thu 21-Dec-23
17			Fri 22-Dec-23
18			Wed 27-Dec-23
19			Thu 28-Dec-23
20			Fri 29-Dec-23
Dec 2023

Cell Formulas
Range	Formula
M1	M1	=LET(t, TEXTAFTER(CELL("filename",$A$1),"]"),d,TEXTSPLIT(t," "),DATE(INDEX(d,2),MONTH(INDEX(d,1)&("-1")),1))
O2:O20	O2	=WORKDAY.INTL(M1-1,SEQUENCE(NETWORKDAYS(M1,EOMONTH(M1,0),Holidays)),,Holidays)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='Dec 2023'!$F$2:$F$4	O2

 

[shawnbull]

Did you use a named range for the Holidays list?
Did you check what range the named list refers to?
If you still wish to use the sheet name for the relevant month, I show in the post below the formulas without the Lambda.

Dates andTime 2024.xlsm
	M	N	O
1	01-Dec-23		Workdays
2			Fri 01-Dec-23
3			Mon 04-Dec-23
4			Tue 05-Dec-23
5			Wed 06-Dec-23
6			Thu 07-Dec-23
7			Fri 08-Dec-23
8			Mon 11-Dec-23
9			Tue 12-Dec-23
10			Wed 13-Dec-23
11			Thu 14-Dec-23
12			Fri 15-Dec-23
13			Mon 18-Dec-23
14			Tue 19-Dec-23
15			Wed 20-Dec-23
16			Thu 21-Dec-23
17			Fri 22-Dec-23
18			Wed 27-Dec-23
19			Thu 28-Dec-23
20			Fri 29-Dec-23
Dec 2023

Cell Formulas
Range	Formula
M1	M1	=LET(t, TEXTAFTER(CELL("filename",$A$1),"]"),d,TEXTSPLIT(t," "),DATE(INDEX(d,2),MONTH(INDEX(d,1)&("-1")),1))
O2:O20	O2	=WORKDAY.INTL(M1-1,SEQUENCE(NETWORKDAYS(M1,EOMONTH(M1,0),Holidays)),,Holidays)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='Dec 2023'!$F$2:$F$4	O2

Thanks. Do use a named range

 

[Peter_SSs]

Thanks. Do use a named range

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[shawnbull]

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

Thank you for the input and apologies for the marked solution. I had marked a solution originally, but assumed after I got further responses that it might not have taken, so did it again.