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)

 

[Fluff]

How about

=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),C2)

 

[searchingforhelp]

How about

=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),C2)

Hi Fluff, sorry about the late reply. Thank you so much for this! I truly appreciate it. I was playing with the formula and was wondering if you can modify the formula regarding row 3. Instead of having row 3 calculate from Date when assignment was reassigned (column B) to Date when assignment was completed (column C). Have the formula only calculate Date originally assigned (Column A) to the date when assignment was reassigned (Column B) and have the formula still work for what I needed help with regarding Row 2 and 4 which is:

1. Formula needs to calculate business days only, excluding weekends
2. Calculate the days from when the assignment was originally assigned (Column A) to the date when assignment was completed (Column C)- this will be row 2
3. 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 will be row 4

Thanking you in advance for your time.

 

[Fluff]

So if B3<C3 you want to calculate the number of days between A3 & B3?

 

[searchingforhelp]

So if B3<C3 you want to calculate the number of days between A3 & B3?

Hi Fluff, Yes. instead of counting the number of days between B3 and C3, calculate the number of days between A3 and B3. However, this should only apply to row 3. As I would need row 2 and row 4 to calculate as it is presently doing. Hope this clarifies and thank you.

 

[Fluff]

Ok, how about

=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))

 

[searchingforhelp]

Ok, how about

=NETWORKDAYS(IF(B2>C2,A2,MAX(A2,B2)),IF(AND(B2<>"",B2<C2),B2,C2))

Thank you Fluff! The Formula works! I will continue to test it on a larger data scale.

Quick Question and this should be the last request. Since you help me solve original assignment, can you help me solve the reassignment with a separate formula that I will place in a separate cell? So, basically it will be same principle but what is needed is:

1. Lets start with row 2. If there is no date when assignment was reassigned (Column B), have (Column E) state “not applicable”.

2. For row 3, if there is a date in Column B and a Date in Column C, calculate the Number of days in Column D

3. If the Date when assignment was reassigned (Column B) exceeds Date when assignment was completed (Column C), in Column E, state “Not applicable”.

This is only for reassignments. Thank you again, you are a life saver.

 

[Fluff]

How about

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

 

[searchingforhelp]

How about

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

Great! The formula did work for "not applicable" but it did not calculate the days in row 3. Is that feasible?

 

[Fluff]

You said for row 3 to calc the days in col D which is what the original formula does.