Get Due Date by counting calendar days but result should exclude weekends and holidays

[jneria]

Hi all,

I am going crazy trying to figure this out. The formula I am using work for some dates but not others, please help me figure out what is wrong.

FIRST FORMULA:

=WORKDAY.INTL(D10, 21, "0000000", StatHolidays!A1:A24)+IF(WEEKDAY(WORKDAY.INTL(D10, 21, "0000000", StatHolidays!A1:A24),2)>5,2,0)+IF(COUNTIF(StatHolidays!A1:A24,WORKDAY.INTL(D10, 21, "0000000", StatHolidays!A1:A24))=1,1,0)

D10 = 2023-12-04
Result = 2023-12-27 (This is correct)

=WORKDAY.INTL(D26, 35, "0000000", StatHolidays!A1:A24)+IF(WEEKDAY(WORKDAY.INTL(D26, 35, "0000000", StatHolidays!A1:A24),2)>5,2,0)+IF(COUNTIF(StatHolidays!A1:A24,WORKDAY.INTL(D26, 35, "0000000", StatHolidays!A1:A24))=1,1,0)

D26 - 2023-05-16
Result = 2023-06-21 (This is incorrect, it should be 2023-06-20)

As you can see, I feel like I am using the exact same formulas, just editing the argument based on what I need. Please help and thank you.

 

[awoohaw]

I'm looking at your question here again:

this just works perfectly! thank you so much. could I ask why the formula in bold had to have a +21 instead of a +42?

=IF(OR(ISNUMBER(MATCH(D10+42,StatHolidays!A1:A24,0)),WEEKDAY(D10+42,2)>5),WORKDAY.INTL(D10+21,1,1,StatHolidays!A1:A24),D10+42)

thank you again!

Sorry, I was referring to the formula you had for C3, I was trying it out for different number of days just to see how it worked, you still used +21 below but everything else says +35 but it worked perfectly.

=IF( OR( ISNUMBER(MATCH(A3+35,$G$1:$G$21,0)), WEEKDAY(A3+35,2)>5), WORKDAY.INTL(A3+21,1,1,$G$1:$G$21), A3+35)

OK, then I have an error there, my bad.

it should be:

=IF( OR( ISNUMBER(MATCH(A3+35,$G$1:$G$21,0)), WEEKDAY(A3+35,2)>5), WORKDAY.INTL(A3+35,1,1,$G$1:$G$21), A3+35)

 

[awoohaw]

this is the correct mini workbook:

Mr excel questions 35.xlsm
	A	B	C	D	E	F	G
1	start date	what it needs to do		what the result should be			2023-04-10
2	2023-12-04	add 21 calendar days but if the result is a weekend or holiday, should result to the next business day instead	2023-12-27	2023-12-27			2023-05-22
3	2023-05-16	add 35 calendar days but if the result is a weekend or holiday, should result to the next business day instead	2023-06-20	2023-06-20			2023-07-03
4							2023-08-07
5							2023-09-04
6							2023-10-09
7							2023-11-13
8							2023-12-25
9							2023-12-26
10							2024-01-01
11							2024-02-12
12							2024-03-29
13							2024-04-01
14							2024-05-20
15							2024-07-01
16							2024-08-05
17							2024-09-02
18							2024-10-14
19							2024-11-11
20							2024-12-25
21							2024-12-26
jneria

Cell Formulas
Range	Formula
C2	C2	=IF( OR( ISNUMBER(MATCH(A2+21,$G$1:$G$21,0)), WEEKDAY(A2+21,2)>5), WORKDAY.INTL(A2+21,1,1,$G$1:$G$21), A2+21)
C3	C3	=IF( OR( ISNUMBER(MATCH(A3+35,$G$1:$G$21,0)), WEEKDAY(A3+35,2)>5), WORKDAY.INTL(A3+35,1,1,$G$1:$G$21), A3+35)

 

[jneria]

I'm looking at your question here again:






OK, then I have an error there, my bad.

it should be:

=IF( OR( ISNUMBER(MATCH(A3+35,$G$1:$G$21,0)), WEEKDAY(A3+35,2)>5), WORKDAY.INTL(A3+35,1,1,$G$1:$G$21), A3+35)

It was working, no matter how the number of days I tried, thank you again!

 

[awoohaw]

It was working, no matter how the number of days I tried, thank you again!

My pleasure. I'm pleased you were able to find a solution here on the Mr. Excel Forum.
And if you think a post in the thread has answered your question, please mark it as the answer.

Best wishes!