If function to exclude weekends and Holidays

[Bazabelle]

Hi,

Not sure how to use this formula from another post; =IF(AND(WORKDAY(C3,0)=WORKDAY(TODAY(),0),E3<>""),E3,"")

Currently I have this one which works perfectly but I want to exclude any Holidays

=IF(WEEKDAY(E4+D6, 2)= 7,E4+D6+1,IF(WEEKDAY(E4+D6,2)= 6,E4+D6+2, E4+D6))

E4 = 16Aug2023
D6 = 6

 

[awoohaw]

what are you trying to accomplish? Are you trying to find the next non weekend/non holiday day's date after a specific number of days?

This excludes all Saturdays, Sundays, and Holidays in a holiday list:

Mr Excel Questions 73.xlsm
	A	B	C	D	E
1
2
3
4	Friday, August 25, 2023				Wednesday, August 16, 2023
5
6	Holiday List			6
7	Friday, August 18, 2023
8
Bazabelle

Cell Formulas
Range	Formula
A4	A4	=WORKDAY.INTL(E4,D6,1,A7)

 

[Bazabelle]

what are you trying to accomplish? Are you trying to find the next non weekend/non holiday day's date after a specific number of days?

This excludes all Saturdays, Sundays, and Holidays in a holiday list:

Mr Excel Questions 73.xlsm
	A	B	C	D	E
1
2
3
4	Friday, August 25, 2023				Wednesday, August 16, 2023
5
6	Holiday List			6
7	Friday, August 18, 2023
8
Bazabelle

Cell Formulas
Range	Formula
A4	A4	=WORKDAY.INTL(E4,D6,1,A7)

Ok, this works, considering that the number of days would be 6 working days where in my formulas it counts 6 days but return to the working day before/after. Thanks!

 

[awoohaw]

So, what did you want? Can you give me all the criteria and the expectation:
Date: ??
Days to Count: ??
Holidays: ??
What are your weekend days (days not worked)?: ??

 

[Bazabelle]

So, what did you want? Can you give me all the criteria and the expectation:
Date: ??
Days to Count: ??
Holidays: ??
What are your weekend days (days not worked)?: ??

Date: 20Dec2023
Days to Count: 6
Holidays: 25Dec2023 to 01Jan2024
What are your weekend days (days not worked)?: Saturday, Sunday

answer would technically be: 26Dec2023 but considering the Holidays it would need to return 02Jan2024

 

[Scott Huish]

I would think it would return Jan. 3, 2024.

Book2
	A	B	C	D	E	F	G	H
1	12/25/2023	6	1/3/2024					Holidays
2								12/25/2023
3								1/1/2024
Sheet1

Cell Formulas
Range	Formula
C1	C1	=WORKDAY(A1,B1,H2:H3)

 

[MARK858]

I'll go with the 5th if the OP wants 6 working days from the 20th

Holidays: 25Dec2023 to 01Jan2024

Book1
	A	B	C	D	E	F	G	H
1	20/12/2023	6	05/01/2024					Holidays
2								25/12/2023
3								26/12/2023
4								27/12/2023
5								28/12/2023
6								29/12/2023
7								30/12/2023
8								31/12/2023
9								01/01/2024
Sheet1

Cell Formulas
Range	Formula
C1	C1	=WORKDAY(A1,B1,H2:H9)

or if the OP wants the 2nd (although adding 6 calendar days seems a bit random to me)

Book1
	A	B	C	D	E	F	G	H
1	20/12/2023	1	02/01/2024					Holidays
2								25/12/2023
3								26/12/2023
4								27/12/2023
5								28/12/2023
6								29/12/2023
7								30/12/2023
8								31/12/2023
9								01/01/2024
Sheet1

Cell Formulas
Range	Formula
C1	C1	=WORKDAY(A1+6,B1,H2:H9)

 

[Bazabelle]

Ok, so with feedback I got above (thank you all) I am almost with the formula I need... so the team wants to have calendar days to be counting, returning on a working day , and exclude the holidays... this formula is bringing me 1 day short, it should return 30Nov2023:

• Start Date: 24Nov2023
• Number calendar days: 6
• Date to return: 30Nov2023

=IF(WEEKDAY(WORKDAY.INTL(E4,D6,17,HOLIDAYS!A:A)-1)=7,WORKDAY.INTL(E4,D6,17,HOLIDAYS!A:A)+1,IF(WEEKDAY(WORKDAY.INTL(E4,D6,17,HOLIDAYS!A:A)-1)=6,WORKDAY.INTL(E4,D6,17,HOLIDAYS!A:A)+2,WORKDAY.INTL(E4,D6,17,HOLIDAYS!A:A)))

The portion to exclude Holidays (which is another part of calculation I need) seems to work with this formula. So just need to fix the including weekends and returning on a working day.

 

[MARK858]

Workday adds the 2nd criteria in days so try to use 5 in my last formula so it reality it is 5 + 1

Book1
	A	B	C	D	E	F	G	H
1	20/12/2023	1	02/01/2024					Holidays
2	24/11/2023		30/11/2023					25/12/2023
3								26/12/2023
4								27/12/2023
5								28/12/2023
6								29/12/2023
7								30/12/2023
8								31/12/2023
9								01/01/2024
Sheet1

Cell Formulas
Range	Formula
C1:C2	C1	=WORKDAY(A1+5,$B$1,$H$2:$H$9)

 

[Bazabelle]

Workday adds the 2nd criteria in days so try to use 5 in my last formula so it reality it is 5 + 1

Book1
	A	B	C	D	E	F	G	H
1	20/12/2023	1	02/01/2024					Holidays
2	24/11/2023		30/11/2023					25/12/2023
3								26/12/2023
4								27/12/2023
5								28/12/2023
6								29/12/2023
7								30/12/2023
8								31/12/2023
9								01/01/2024
Sheet1

Cell Formulas
Range	Formula
C1:C2	C1	=WORKDAY(A1+5,$B$1,$H$2:$H$9)

when I use this it breaks because my cell has "6" instead of "1" and I cannot change this, I need to leave it to show it is 6 calendar days.