Thread: IF Statement using Networkdays.intl formula Thanks: 0 Likes: 0

1. IF Statement using Networkdays.intl formula

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:

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

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?

2. Re: IF Statement using Networkdays.intl formula

Hi,

try something like this:

JKLMNOPQRSTUV
5Fiscal Start20-9-201928-9-20191-10-201926-10-20191-11-201923-11-20191-12-20191-1-202025-1-2020
6Fiscal End27-9-201930-9-201925-10-201931-10-201922-11-201930-11-201931-12-201924-1-202031-1-2020
7Net Wrk Days61184154211675
8POPSPOPEWrk Hrs
920-9-20193-11-20198Program Mgr488144328-112-144-312
104881523280000

Sheet1

Worksheet Formulas
CellFormula
N10=IF(OR(N6<\$J9,\$K9<N5),0,NETWORKDAYS.INTL(MAX(\$J9,N5),MIN(\$K9,N6),1,))*\$L9

3. Re: IF Statement using Networkdays.intl formula

Thank you so much!