Date Calculation

[fulanito_uk]

Good morning,
I have a question about a sheet I am doing I will give some context.

I have 3 possible status options OPEN, MAYBE, CLOSED,
If the status is OPEN or MAYBE i need the NETWORKDAYS to calculate from DATE 1 (A) and DATE 3 (D)
If the status is CLOSED I need the NETWORKDAYS to calculate from DATE 2 (B) and DATE 3 (D).

If you need any more information for my side please let me know and Thank you very much for your help.

 

[Fluff]

How about
=NETWORKDAYS(IF(C2="closed",B2,A2),D2)

 

[CA_Punit]

Book1
	A	B	C	D	E
1	DATE1	DATE 2	STATUS	DATE 3	NETWORK DAYS
2	01/01/2020		OPEN	02/02/2020	23
3	02/01/2020		MAYBE	02/02/2020	22
4	03/01/2020	01/01/2020	CLOSED	02/02/2020	23
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=IF(OR(C2={"Open","Maybe"}),NETWORKDAYS.INTL(A2,D2,1),NETWORKDAYS.INTL(B2,D2,1))

 

[fulanito_uk]

Thanks so much for getting back to me, your formula worked fine on my test table but when I try to move it to my real one the formula doesn't seem to work..
if G5 says CERRADO (closed in spanish) it should take the dates from H5 and I5 if any other text is in G5 should take the dates from H5 and B1.

Thx! Any ideas what i'm doing wrong?

 

[CA_Punit]

I suppose it is returning the correct solution.

 

[fulanito_uk]

umm.. as its "Cerrado" should be days between 05/03/2020 and 05/03/2020 = 0 days ?

 

[Fluff]

You need to swap the H5 & B5 in the formula.

 

[fulanito_uk]

b5?

 

[Fluff]

Oops misread & was looking at col C
=NETWORKDAYS(H5,IF(G5="cerrado",I5,B$1))

 

[fulanito_uk]

Oops misread & was looking at col C
=NETWORKDAYS(H5,IF(G5="cerrado",I5,B$1))

That one worked! thanks so much for your help!