Networkdays between multiple date ranges (without overlap and gaps)

[Fathalla]

Can any one help me pls with an Excel formula to determine the networkdays between multiple date ranges, without overlap and gaps.
Example:
Task 1 : Starts 1/4/2023 Ends 16/4/2023
Task 2 : Starts 15/7/2023 Ends 5/8/2023
Task 3 : Starts 1/8/2023 Ends 10/8/2023

 

[Wookiee]

I'm not sure what you mean by "without overlaps or gaps", but this is how I set up a calculation for network days which excludes a list of holidays (found in cells F1:H1).

Assuming your dates exist in Columns A and B and the first row contains your headers, enter the following formula in cell C2:

=NETWORKDAYS($A2,$B2,$F$1:$H$1)

I hope this was what you were looking for.

 

[Fathalla]

Thank you for your answer. Actually what I meant is the following: I need a formula/function to sum up the network days without calculating any overlapping between date ranges and without calculating any gaps between the date ranges. That is the, the result I think an our example should yield to 29 days.

Wookiee​

 

[Wookiee]

Thank you for your answer. Actually what I meant is the following: I need a formula/function to sum up the network days without calculating any overlapping between date ranges and without calculating any gaps between the date ranges. That is the, the result I think an our example should yield to 29 days.

Wookiee​

I apologize for misunderstanding. I do not know of a way to calculate days wherein multiple date ranges may have overlap.

 

[Dave Patton]

T202306a.xlsm
	B	C	D	E
1
2	01-04-23	16-04-23	29	29
3	15-07-23	05-08-23
4	01-08-23	10-08-23
5
3d

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS(B2,C2,1)+NETWORKDAYS(B3,C3,1)+NETWORKDAYS(MAX(C3,B4),C4,1)
E2	E2	=NETWORKDAYS(B2,C4,1)-NETWORKDAYS(C2,B3,1)

 

[Fathalla]

T202306a.xlsm
	B	C	D	E
1
2	01-04-23	16-04-23	29	29
3	15-07-23	05-08-23
4	01-08-23	10-08-23
5
3d

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS(B2,C2,1)+NETWORKDAYS(B3,C3,1)+NETWORKDAYS(MAX(C3,B4),C4,1)
E2	E2	=NETWORKDAYS(B2,C4,1)-NETWORKDAYS(C2,B3,1)

Thank you Dave, much appreciated, in case there are a big list of ranges, like of example a project schedule with 100 tasks each of which has a start date and an end date, is there a formula or perhaps an array formula to get the network days without the gaps and overlapping

 

[Dave Patton]

The formula in E2 might help.
It would help if you posted an informative example with say 20 rows. You can use the forum's tool named XL2BB.
Are you using Excel 2016?

 

[Fathalla]

The formula in E2 might help.
It would help if you posted an informative example with say 20 rows. You can use the forum's tool named XL2BB.
Are you using Excel 2016?

Currently I am using Excel 365, Sorry i couldn't use the XL2BB add-in for security reasons i guess, (macros are not verified and hence disabled)
Attached a sample image !

 

[Dave Patton]

Excel has a variety of new functions that you may be able to use.

Expert users may provide much more advanced help but this may get you started.

T202306a.xlsm
	C	D	E	F	G	H	I	J	K	L	M
1			Workdays
2	2-Jan-23	13-Jan-23	10		2-Jan-23	44943	44958	45091	44963		57
3	17-Jan-23	23-Jan-23	5		3-Jan-23	44944	44959	45092	44964
4	1-Feb-23	15-Feb-23	11		4-Jan-23	44945	44960	45093	44965
5	14-Jun-23	17-Jul-23	24		5-Jan-23	44946	44963	45096	44966
6	5-Feb-23	25-Feb-23	15		6-Jan-23	44949	44964	45097	44967
7					9-Jan-23		44965	45098	44970
8					10-Jan-23		44966	45099	44971
9					11-Jan-23		44967	45100	44972
10					12-Jan-23		44970	45103	44973
11					13-Jan-23		44971	45104	44974
12							44972	45105	44977
13								45106	44978
14								45107	44979
15								45110	44980
16								45111	44981
17								45112
18								45113
19								45114
20								45117
21								45118
22								45119
23								45120
24								45121
25								45124
4dd

Cell Formulas
Range	Formula
G2:G11	G2	=WORKDAY.INTL(C2-1,SEQUENCE(10,,1.1),1)
H2:H6	H2	=WORKDAY.INTL(C3-1,SEQUENCE(E3,,1,1),1)
I2:I12	I2	=WORKDAY.INTL(C4-1,SEQUENCE(E4,,1,1),1)
J2:J25	J2	=WORKDAY.INTL(C5-1,SEQUENCE(E5,,1,1),1)
K2:K16	K2	=WORKDAY.INTL(C6-1,SEQUENCE(E6,,1,1),1)
M2	M2	=COUNT(UNIQUE(VSTACK(G2#,H2#,I2#,J2#,K2#)))
E2:E6	E2	=NETWORKDAYS.INTL(C2,D2,1)
Dynamic array formulas.