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

 

[FormR]

Hi, if you are trying to return the following workday if the date in Z5 is a non working day, but just return the date that is in Z5 if it is already a working day then you could try:
=WORKDAY.INTL(Z5-1,1,1,'date calcs'!$F$5:$F$19)

If you're trying to do something else, then try to explain in words what it is you are trying to do.

 

[Dave Patton]

Review the following and review Excel's help files.
You can then adapt the information to your specific requirements.
You can post an extract of your sheet with the forum's tool named XL2BB.

T202401a.xlsm
	A	B	C	D
1				Holidays
2	Wed 20-Dec-23			25-12-23
3	Thu 21-Dec-23			26-12-23
4	Fri 22-Dec-23			01-01-24
5	Wed 27-Dec-23
6	Thu 28-Dec-23
7	Fri 29-Dec-23
8	Tue 02-Jan-24
9	Wed 03-Jan-24
3d

Cell Formulas
Range	Formula
A3:A9	A3	=WORKDAY.INTL(A2,1,1,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='3d'!$D$2:$D$4	A3:A9

 

[Dave Patton]

There was no feedback; we must have answered the question.
For interested people who read the thread, I built a function and I also showed some of the components.
The relevant month is derived from the sheet name.
The holidays relevant to the relevant month would have to included in the Holiday range.

In Name Manager
- enter a name that you prefer for the function
- Value paste the Lambda information

T202401a.xlsm
	M
1	Mon 02-Dec-24
2	Tue 03-Dec-24
3	Wed 04-Dec-24
4	Thu 05-Dec-24
5	Fri 06-Dec-24
6	Mon 09-Dec-24
7	Tue 10-Dec-24
8	Wed 11-Dec-24
9	Thu 12-Dec-24
10	Fri 13-Dec-24
11	Mon 16-Dec-24
12	Tue 17-Dec-24
13	Wed 18-Dec-24
14	Thu 19-Dec-24
15	Fri 20-Dec-24
16	Mon 23-Dec-24
17	Tue 24-Dec-24
18	Wed 25-Dec-24
19	Thu 26-Dec-24
20	Fri 27-Dec-24
21	Mon 30-Dec-24
22	Tue 31-Dec-24
23
Dec 2024

Cell Formulas
Range	Formula
M1:M22	M1	=wDays()
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='Dec 2024'!$J$2:$J$4	M1

Lambda Functions
Name	Formula
SheetName	=LAMBDA(TEXTAFTER(CELL("filename",!$A$1),"]"))
wDays	=LAMBDA(LET(d,TEXTSPLIT(SheetName()," "),s,DATE(INDEX(d,2),MONTH(INDEX(d,1)&("-1")),1)-1,WORKDAY.INTL(s,SEQUENCE(NETWORKDAYS.INTL(s,EOMONTH(s,1),1,Holidays)),1,Holidays)))

T202401a.xlsm
	G	H	I	J
1	Month	Year	Workdays	Holidays
2	Dec	2024	22	Mon 25-Dec-23
3				Tue 26-Dec-23
4	Sat 30-Nov-24	Tue 31-Dec-24		Mon 01-Jan-24
5
Dec 2024

Cell Formulas
Range	Formula
G2:H2	G2	=TEXTSPLIT(SheetName()," ")
I2	I2	=LET(d,TEXTSPLIT(SheetName()," "),b,DATE(INDEX(d,2),MONTH(INDEX(d,1)&("-1")),1)-1,NETWORKDAYS.INTL(b,EOMONTH(b,1),1,Holidays))
G4	G4	=LET(d,TEXTSPLIT(SheetName()," "),DATE(INDEX(d,2),MONTH(INDEX(d,1)&("-1")),1)-1)
H4	H4	=EOMONTH(G4,1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='Dec 2024'!$J$2:$J$4	I2

 

[Dave Patton]

T202401a.xlsm
	M
1	Fri 01-Mar-24
2	Mon 04-Mar-24
3	Tue 05-Mar-24
4	Wed 06-Mar-24
5	Thu 07-Mar-24
6	Fri 08-Mar-24
7	Mon 11-Mar-24
8	Tue 12-Mar-24
9	Wed 13-Mar-24
10	Thu 14-Mar-24
11	Fri 15-Mar-24
12	Mon 18-Mar-24
13	Tue 19-Mar-24
14	Wed 20-Mar-24
15	Thu 21-Mar-24
16	Fri 22-Mar-24
17	Mon 25-Mar-24
18	Tue 26-Mar-24
19	Wed 27-Mar-24
20	Thu 28-Mar-24
21	Fri 29-Mar-24
Mar 2024

Cell Formulas
Range	Formula
M1:M21	M1	=wDays()
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='Mar 2024'!$J$2:$J$4	M1

Lambda Functions
Name	Formula
Date1	=LAMBDA(LET(t,TEXTSPLIT(SheetName()," "),DATE(INDEX(t,2),MONTH(INDEX(t,1)&("-1")),1)))
wDays	=LAMBDA(WORKDAY.INTL(Date1()-1,SEQUENCE(NETWORKDAYS(Date1(),EOMONTH(Date1(),0),Holidays)),,Holidays))

 

[shawnbull]

Hi, if you are trying to return the following workday if the date in Z5 is a non working day, but just return the date that is in Z5 if it is already a working day then you could try:
=WORKDAY.INTL(Z5-1,1,1,'date calcs'!$F$5:$F$19)

If you're trying to do something else, then try to explain in words what it is you are trying to do.

Thank you. Apologies our system was offline so was unable to reply sooner. Have managed a workaround that seems to work.

 

[shawnbull]

Thank you. Apologies our system was offline so was unable to reply sooner. Have managed a workaround that seems to work.

 

[shawnbull]

Review the following and review Excel's help files.
You can then adapt the information to your specific requirements.
You can post an extract of your sheet with the forum's tool named XL2BB.

T202401a.xlsm
	A	B	C	D
1				Holidays
2	Wed 20-Dec-23			25-12-23
3	Thu 21-Dec-23			26-12-23
4	Fri 22-Dec-23			01-01-24
5	Wed 27-Dec-23
6	Thu 28-Dec-23
7	Fri 29-Dec-23
8	Tue 02-Jan-24
9	Wed 03-Jan-24
3d

Cell Formulas
Range	Formula
A3:A9	A3	=WORKDAY.INTL(A2,1,1,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='3d'!$D$2:$D$4	A3:A9

Thank you. Apologies our system was offline so was unable to reply sooner. Have managed a workaround that seems to work.
I do like the concept of using Lambda, haven't tried it out yet but am definitely going to give it a go so that my knowledge in this area increases

 

[Dave Patton]

Thanks for the feedback.
It is good to hear that you solved your challenge. What workaround did you use?

Dave

 

[shawnbull]

Thanks for the feedback.
It is good to hear that you solved your challenge. What workaround did you use?

I cheated. Simply grabbed the spreadsheet that I have been using, renamed it and then deleted the data, and changed the tab year to 2024 and 2025. Dates seemed to have copied over successfully.
Oddity is that I can type in exactly the same formula in a new spreadsheet, but will not recognize the holidays, when saved in the new spreadsheet.