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

hi,

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

sprint starts 15-JUN-23, ends 28-JUN-23. 19th June 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 out of the possible 9 days for that 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.

i am drawing a blank. thanks for the assistance. make @daddylonglegs proud with a fabulous date / time formula.

You will need a way to build a list for each team member combining both holidays and vacation days. There are a couple of ways to do this, but a little more convoluted using older versions of Excel. Ultimately the solution is very dependent on your specific data layout. Attach a file with some sample data (do not include any private data), and let us know where to find the list of holidays, the lists of vacation days for each team member, and where you want the result to show up (as in 6 days in your J Doe example).

hi, i have uploaded a file. i hope this helps visualise what i am looking for. thanks.

I am using excel 2019.

posted elsewhere

This is not in the same league with a daddylonglegs formula, but perhaps it will prompt someone to come up with one.
1. Column P is a list of dates for the sprints: =B2 and =SUM(P2,1)
2. Columns Q:R display 1 or 0 depending on the active sprint: =SUMPRODUCT(($A$2:$A$3=P$1)*($B$2:$B$3<=$R2)*($C$2:$C$3>=$R2))
3. Columns S:T display the availably of the employee using:
=IF(WEEKDAY($P2,2)>=6,"",IF(NOT(OR(COUNTIFS($H$2:H$3,$P2),SUMPRODUCT(($J$2:$J$3=S$1)*($K$2:$K$3<=$P2)*($L$2:$L$3>=$P2)))),INDEX($Q$1:$R$1,MATCH(1,$Q2:$R2,0)),""))
4. The totals are displayed in M2:N3 using: =SUMPRODUCT(($S$1:$T$1=$J2)*($S$2:$T$29=M$1))
Let us know if you have any questions.

thanks JeteMc, DLL's are indeed long legs to fill, i was just trying to encourage a solid try :-)

thank you for offering a solution.

i had also asked the question here https://tinyurl.com/4kfvyaf6

awoohaw provided a beautiful solution that we can all be proud of.

Hello,

Glad you have the solution. But just want to see if the solution provided works on all situations.

Eg: What if the vacation start: 6/15/23 end: 6/27/23 AND start: 6/1/23 end: 6/15/23, what are your expected results, (1,9 and 8,9) respectively instead of (1,12 and 8,19)? Then try this formula.

M2:

=MIN(D$2,MAX(0,NETWORKDAYS(B$2,K2-1,H$2:H$16))+MAX(0,NETWORKDAYS(L2+1,C$2,H$2:H$16)))

N2:

=MIN(D$3,MAX(0,NETWORKDAYS(B$3,K2-1,H$2:H$16))+MAX(0,NETWORKDAYS(L2+1,C$3,H$2:H$16)))

hi haseeb, thank you so very much for catching that important gap!! indeed, i would like to see {1,9; 8,9} so i am very grateful that you took the time to help.