Projecting End date by accounting for holidays

[BB77]

Hello All,

Looking for some support with this one. I will do my best to explain it. I am trying to dynamically calculate the end date of a program of study by using the start date and the number of program days (easy enough), while dynamically including/accounting for the established holidays that would fall within the length of that program.

So, for example, let's say the program starts on January 1st and is 20 days long, however, there is a holiday on 1/10 and day 1/19, so the end date really needs to be 22 days after the start date instead of 20.

Another example, a program that is 20 days long starts on 1/11 with the same holidays as above. For this entry, the calculation would return an end date based on 21 days because it starts after the holiday on 1/10.

The holiday dates are pre-established. Currently, I am attempting to have the holidays listed in one table and then have the program start date and number of days be what someone would enter to have the end date dynamically generated from referencing the list of holidays that would fall within the range of that program. And, I am lost bouncing between some VLOOKUP, INDEX, MATCH, and COUNTIFS. LOL

Any help would be appreciated.

Brad

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1							Holidays
2	11/01/2024	20	09/02/2024				10/01/2024
3	01/01/2024	20	31/01/2024				19/01/2024
Data

Cell Formulas
Range	Formula
C2:C3	C2	=WORKDAY(A2,B2,$G$2:$G$5)

 

[BB77]

Thank you! I forgot to mention that I had tried WORKDAY, but I have to count every day in the week. I think I found something that works, although it isn't pretty. Here is what I have ( can't install the add-in on my excel so its pictures only. Sorry.)

Holiday Reference Table:

Calculations:

Temp Grad Date is =B2+(B3*7)+Q18
Holiday Rows to Calculate: =MATCH(B4,Table18112[End Date],1)
Total Holidays: =SUM(K2:OFFSET(K1,B6,0))

Final Grad Date: =SUM(B4+B7)

It seems to work. Now trying to see what I can consolidate.

 

[Fluff]

In that case how about

Fluff.xlsm
	A	B	C	D	E	F	G
1							Holidays
2	11/01/2024	20	01/02/2024				10/01/2024
3	01/01/2024	20	23/01/2024				19/01/2024
Data

Cell Formulas
Range	Formula
C2:C3	C2	=WORKDAY.INTL(A2,B2,"0000000",$G$2:$G$5)

 

[BB77]

Ok, I consolidated it down to

Total Holidays: =SUM(K2:OFFSET(K1,(MATCH(SUM(B2+(B3*7)),Table18112[End Date],1)),0))
Final Grad Date: =SUM(SUM(B2+(B3*7))+B5)

Thank you!!!!!