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]

your weekend reference for Sat & Sun weekends should just be a 1 or "1000001".

but if you can use the xl2bb add in a post a mini workbook it would be helful if what I have above is not working for you. Link is below.

 

[jneria]

your weekend reference for Sat & Sun weekends should just be a 1 or "1000001".

but if you can use the xl2bb add in a post a mini workbook it would be helful if what I have above is not working for you. Link is below.

Hello and thank you so much for your time, here it is.

Book1
	A	B	C	D
3	start date	what it needs to do	what the result should be	what the result is using formula
4	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
5	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-21
Sheet1

Cell Formulas
Range	Formula
D4	D4	=WORKDAY.INTL(A4, 21, "0000000", StatHolidays!A1:A24)+IF(WEEKDAY(WORKDAY.INTL(A4, 21, "0000000", StatHolidays!A1:A24),2)>5,2,0)+IF(COUNTIF(StatHolidays!A1:A24,WORKDAY.INTL(A4, 21, "0000000", StatHolidays!A1:A24))=1,1,0)
D5	D5	=WORKDAY.INTL(A5, 35, "0000000", StatHolidays!A1:A24)+IF(WEEKDAY(WORKDAY.INTL(A5, 35, "0000000", StatHolidays!A1:A24),2)>5,2,0)+IF(COUNTIF(StatHolidays!A1:A24,WORKDAY.INTL(A5, 35, "0000000", StatHolidays!A1:A24))=1,1,0)

 

[jneria]

your weekend reference for Sat & Sun weekends should just be a 1 or "1000001".

but if you can use the xl2bb add in a post a mini workbook it would be helful if what I have above is not working for you. Link is below.

by the way, here are the holidays listed in my StatHolidays sheet

2023-04-10​

2023-05-22​

2023-07-03​

2023-08-07​

2023-09-04​

2023-10-09​

2023-11-13​

2023-12-25​

2023-12-26​

2024-01-01​

2024-02-12​

2024-03-29​

2024-04-01​

2024-05-20​

2024-07-01​

2024-08-05​

2024-09-02​

2024-10-14​

2024-11-11​

2024-12-25​

2024-12-26​

 

[awoohaw]

you also need to post the worksheet StatHolidays

 

[jneria]

you also need to post the worksheet StatHolidays

Here it is:

Book1
	A
1	2023-04-10
2	2023-05-22
3	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
StatHolidays

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A21	Cell Value	duplicates	text	NO

 

[awoohaw]

try this:
i move the holidays to my worksheet.

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+21,1,1,$G$1:$G$21), A3+35)

 

[jneria]

try this:
i move the holidays to my worksheet.

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+21,1,1,$G$1:$G$21), A3+35)

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!

 

[awoohaw]

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!

Your requirement was for 21 days not 42. So, I'm not really sure how 42 is being considered? Could you explain, did I miss something?

 

[jneria]

Your requirement was for 21 days not 42. So, I'm not really sure how 42 is being considered? Could you explain, did I miss something?

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)