need to compute networkdays but include individual's vacations, as well as federal holiday

[icestationzbra]

hi,

for computing availability of a team member during an Agile Sprint, i have this situation:

sprint1 starts 15-JUN-23, ends 28-JUN-23. 19th June is US Federal Holiday.

sprint2 starts 29-JUN-23, ends 12-JUL-23. 4th July is US Federal Holiday.

J Doe is on vacation between 25-JUN-23 and 4-JUL-23 (inclusive).
J Doe's availability during the sprint is 6 days for either sprint, i would like the formula to tell me.

as you can imagine, there are several team members, with different vacation schedules, and there are many more holidays during subsequent sprints (e.g., 4th of July for the next sprint). so a formula that would be dynamic enough to handle all that is desired.



thanks in advance.



posted need to compute networkdays but include individual's vacations, as well as federal holiday also

 

[awoohaw]

what version of excel are you using?

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Spring	Start	End	Networkdays				Holidays		Vacation	From	To	Sprint1 Availability	Sprint2 Availability
2	Sprint1	2023-06-15	2023-06-28	6				2023-06-19			2023-06-25	2023-07-04
3	Sprint2	2023-06-29	2023-07-12	6				2023-07-04			2023-06-25	2023-07-04
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=NETWORKDAYS.INTL(B2,C2,1,H2:H3)-IF(OR(C2>K2,B2>L2),NETWORKDAYS.INTL(MAX(B2,K2),MIN(C2,L2),1,$H$2:$H$3))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[icestationzbra]

hi folks, Excel 2019

 

[icestationzbra]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Spring	Start	End	Networkdays				Holidays		Vacation	From	To	Sprint1 Availability	Sprint2 Availability
2	Sprint1	2023-06-15	2023-06-28	6				2023-06-19			2023-06-25	2023-07-04
3	Sprint2	2023-06-29	2023-07-12	6				2023-07-04			2023-06-25	2023-07-04
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=NETWORKDAYS.INTL(B2,C2,1,H2:H3)-IF(OR(C2>K2,B2>L2),NETWORKDAYS.INTL(MAX(B2,K2),MIN(C2,L2),1,$H$2:$H$3))

thank you, i will give this a shot and let you know.

 

[icestationzbra]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Spring	Start	End	Networkdays				Holidays		Vacation	From	To	Sprint1 Availability	Sprint2 Availability
2	Sprint1	2023-06-15	2023-06-28	6				2023-06-19			2023-06-25	2023-07-04
3	Sprint2	2023-06-29	2023-07-12	6				2023-07-04			2023-06-25	2023-07-04
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=NETWORKDAYS.INTL(B2,C2,1,H2:H3)-IF(OR(C2>K2,B2>L2),NETWORKDAYS.INTL(MAX(B2,K2),MIN(C2,L2),1,$H$2:$H$3))

thank you sir, this formula is working out for me. i am attempting to mould it to the work spreadsheet's layout now.

my sincere thanks to you.

 

[awoohaw]

thank you sir, this formula is working out for me. i am attempting to mould it to the work spreadsheet's layout now.

my sincere thanks to you.

You are welcome. I am pleased you found an answer.
But, I think the one I gave you is wrong. I am revisiting it.

 

[Fluff]

In future please post links to all sites where you have asked this question.
Also posted here need to compute networkdays but include individual's vacations, as well as federal holiday - OzGrid Free Excel/VBA Help Forum

 

[awoohaw]

Here is an excel mini workbook with a more comprehensive formula:

Mr excel questions 40.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Spring	Start	End	Networkdays				Holidays		Vacation	From	To	Sprint1 Availability	Sprint2 Availability
2	Sprint1	2023-06-15	2023-06-28	6	FALSE	TRUE	TRUE	2023-06-19			2023-06-25	2023-07-04
3	Sprint2	2023-06-29	2023-07-12	6	TRUE	FALSE	TRUE	2023-07-04			2023-06-25	2023-07-04
4		2019-01-01	2023-08-01	1191	TRUE	FALSE	TRUE				2019-01-01	2019-01-07
5		2023-01-01	2023-08-01	152	FALSE	FALSE	FALSE				2019-01-01	2019-01-07
icestationszbra

Cell Formulas
Range	Formula
D2:D5	D2	=NETWORKDAYS.INTL(B2,C2,1,H2:H3)-IF(OR(AND(B2>=K2,B2<=L2),AND(C2>=K2,C2<=L2)),NETWORKDAYS.INTL(MAX(B2,K2),MIN(C2,L2),1,$H$2:$H$3),0)
E2:E5	E2	=AND(B2>=K2,B2<=L2)
F2:F5	F2	=AND(C2>=K2,C2<=L2)
G2:G5	G2	=OR(AND(B2>=K2,B2<=L2),AND(C2>=K2,C2<=L2))