IF Statement using Networkdays.intl formula

[CapeSable]

I am trying to build a formula that will calculate after determining if a start and end date are equal to or between a calendar (fiscal) start and end date.

The formula I have is:
=IF(AND($J$9<=N$4,$K$9>=N$5)*1=1,(N$6*$L$9),IF(AND($J$9<=N$4,$K$9>=N$5)*1=0,IF(AND($J$9<=N$4,$K$9>=N$5)*1=0,(NETWORKDAYS.INTL(N$4,$K$9,1,Holidays)*$L$9),0)))

My worksheet looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fiscal Start[/TD]
[TD]9/20/19[/TD]
[TD]9/28/19[/TD]
[TD]10/1/19[/TD]
[TD]10/26/19[/TD]
[TD]11/1/19[/TD]
[TD]11/23/19[/TD]
[TD]12/1/19[/TD]
[TD]1/1/20[/TD]
[TD]1/25/20[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fiscal End[/TD]
[TD]9/27/19[/TD]
[TD]9/30/19[/TD]
[TD]10/25/19[/TD]
[TD]10/31/19[/TD]
[TD]11/22/19[/TD]
[TD]11/30/19[/TD]
[TD]12/31/19[/TD]
[TD]1/24/20[/TD]
[TD]1/31/20[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Net Wrk Days[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]75[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]POPS[/TD]
[TD]POPE[/TD]
[TD]Wrk Hrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]09/20/19[/TD]
[TD]11/03/19[/TD]
[TD]8[/TD]
[TD]Program Mgr[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]144[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]-112[/TD]
[TD="align: center"]-144[/TD]
[TD="align: center"]-312[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first part of the formula is calculating as desired, however, I am not getting a "0" result once the POP dates are out of range of the fiscal start and end dates.

Can someone please advise how to fix the formula or suggest another formula to get the desired data?

 

[jorismoerings]

Hi,

try something like this:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V
5				Fiscal Start	20-9-2019	28-9-2019	1-10-2019	26-10-2019	1-11-2019	23-11-2019	1-12-2019	1-1-2020	25-1-2020
6				Fiscal End	27-9-2019	30-9-2019	25-10-2019	31-10-2019	22-11-2019	30-11-2019	31-12-2019	24-1-2020	31-1-2020
7				Net Wrk Days	6	1	18	4	15	4	21	16	75
8	POPS	POPE	Wrk Hrs
9	20-9-2019	3-11-2019	8	Program Mgr	48	8	144	32	8	-112	-144	-312
10					48	8	152	32	8	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
N10	=IF(OR(N6<$J9,$K9),0,NETWORKDAYS.INTL(MAX($J9,N5),MIN($K9,N6),1,))*$L9

<n5),0,networkdays.intl(max($j9,n5),min($k9,n6),1,holidays))*$l9

<n5),0,networkdays.intl(max($j9,n$5),min($k9;n$6),1, holidays))*$l9<="" html=""></n5),0,networkdays.intl(max($j9,n$5),min($k9;n$6),1,></n5),0,networkdays.intl(max($j9,n5),min($k9,n6),1,holidays))*$l9

 

[CapeSable]

Thank you so much!