Combining NETWORKDAYS with IF, THEN, OR formulas

[searchingforhelp]

I am having trouble combining formulas and deciphering which formula is best for what is needed. I am currently using the NETWORKDAYS formula, but I cannot connect it with what I am trying to accomplish due to my skill sets.

What I need assistance in is as follows:

1. the formula needs to calculate business days only, excluding weekends.

2. I would need to calculate the days from when the assignment was originally assigned (Column A) to the date when assignment was completed (Column C). However, if there is a reassignment date (column B), have the formula automatically adjust to calculate the reassignment date (Column B) to when the assignment was completed (column C).

3. Lastly, if the reassignment date (Column B) exceeds the assignment completion date, automatically calculate original assigned date (Column A) from when the assignment was completed.

This is where I am stuck. Combining 1,2 and 3 into one formula so I can drag down as more data is entered on the sheet.

Thanking you in advance for any help you can provide.

Example.xlsx
	A	B	C	D
1	Date when Originally assigned	Date when assignment was reassigned	Date when assignment was completed	Number of Days
2	3/10/2021		3/12/2021	3
3	3/10/2021	3/11/2021	3/18/2021	6
4	3/10/2021	3/15/2020	3/11/2021	2
Sheet1

Cell Formulas
Range	Formula
D2,D4	D2	=NETWORKDAYS(A2,C2)
D3	D3	=NETWORKDAYS(B3,C3)

 

[searchingforhelp]

How about

=IF(OR(B2="",B2>B3),"not applicable","")

Hi Fluff, yes you are correct. That is for the original assignment. Now what is needed is a formula to focus only on reassignment. You did help with not applicable for row 2 and 4, but if there is a instance like in row 3 that will need to be calculated. Hope this clarifies and again thank you for your time. This will be the last request.

 

[Fluff]

In that case use

=IF(OR(B2="",B2>C2),"not applicable",D2)

 

[searchingforhelp]

Hi Fluff, thank you for your patience. I received the error attached; specifically with row 3.

 

[Fluff]

Where are you putting that formula?

 

[searchingforhelp]

Where are you putting that formula?

Hi Fluff, to help, see below. I also noticed two things.

1. Column E row 3 is incorrect in the calculation. Perhaps it is because there is no =NETWORKDAYS formula imbedded into =IF(OR(B3="",B3>C3),"not applicable",D3). That would need to be required for accurate days calculation for the reassigned.

2. I am thinking that =IF(OR(B3="",B3>C3),"not applicable",D3) would need to focus only on column B and C and not have D as a row to calculate as Column E row 3 is incorrect.

3. Column F is an example of what I am needing to solve this issue.

Copy of Example 2.xlsx
	A	B	C	D	E	F	G	H	I
1	Date when Originally assigned	Date when assignment was reassigned	Date when assignment was completed	Number of Days for Originally assigned (Column A) to Date when assignment was completed (Column C)	Formula: =IF(OR(B2="",B2>C2),"not applicable",D2)	Date when assignment was reassigned (Column B) to Date when assignment was completed (Column C)
2	3/10/2021		3/12/2021	3	not applicable	Not applicable
3	3/10/2021	3/11/2021	3/18/2021	1	1	6	<-Networkdays formula
4	3/10/2021	3/15/2021	3/10/2021	1	not applicable	Not applicable
Sheet1 (4)

Cell Formulas
Range	Formula
D2:D4	D2	=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))
E2:E4	E2	=IF(OR(B2="",B2>C2),"not applicable",D2)
F3	F3	=NETWORKDAYS(B3,C3)

Again, I truly appreciate your patience. Please let me know where I can write a great review on your behalf.

 

[Fluff]

In that case just replace the D2 with the formula that I originally supplied.

 

[searchingforhelp]

In that case just replace the D2 with the formula that I originally supplied.

Okay cool. I hope that I have not exceed the amount of time you are allowed to allocate to one individual. Please let me know if that is the case.

In regards to you last statement, if I go back to the original formula of (=IF(OR(B2="",B2>C2),"not applicable","") without D, then how can the formula count the days from Column B and C? Is there a way to place a networkdays formula in =IF(OR(B2="",B2>C2),"not applicable",""). I tried =NETWORKDAYS(IF(OR(B2="",B2>C2),"not applicable","") but the formula is missing an opening or closing parenthesis which I cannot figure out. This pertains to column F

Lastly, I attempted to work with the original formula of =NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2)) that you provided at the very first response. I noticed in Column D the numbers of days are calculated incorrectly when matching it with column E that only has the formula =Networkdays. Please see image below.

Copy of Example 2.xlsx
	A	B	C	D	E	F
1	Date when Originally assigned	Date when assignment was reassigned	Date when assignment was completed	Formula that was provided: =NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))	NETWORKDAYS formula only for column A and B	Date when assignment was reassigned (Column B) to Date when assignment was completed (Column C)
2	3/10/2021		3/12/2021	3	3	not applicable
3	3/10/2021	3/11/2021	3/18/2021	1	2
4	3/10/2021	3/15/2021	3/10/2021	1	4	not applicable
Sheet1 (4)

Cell Formulas
Range	Formula
D2:D4	D2	=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))
E2	E2	=NETWORKDAYS(A2,C2)
F2:F4	F2	=IF(OR(B2="",B2>C2),"not applicable","")
E3:E4	E3	=NETWORKDAYS(A3,B3)

 

[Fluff]

I meant like

=IF(OR(B2="",B2>C2),"not applicable",NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),C2))

 

[searchingforhelp]

Fantastic!! Awesome. The formula works perfectly regarding column F . Hopefully this is the last reply .

Any luck regarding column D as the count is incorrect. D3 and 4 should be 2 and 4 not 1 and 1.

Copy of Example 2.xlsx
	A	B	C	D	E
1	Date when Originally assigned	Date when assignment was reassigned	Date when assignment was completed	Formula that was provided: =NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))	NETWORKDAYS formula only for column A and B
2	3/10/2021		3/12/2021	3	3
3	3/10/2021	3/11/2021	3/18/2021	1	2
4	3/10/2021	3/15/2021	3/10/2021	1	4
Sheet1 (4)

Cell Formulas
Range	Formula
D2:D4	D2	=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))
E2	E2	=NETWORKDAYS(A2,C2)
E3:E4	E3	=NETWORKDAYS(A3,B3)

 

[Fluff]

Why should D4 be not & not 1, or have you moved the goalposts again?