Formula to calculate Network Days not working properly

[JayB0730]

Hello,

I've been using the following formula for months now without problem (or so I thought) & it worked in the month of August (which had Five Fridays in the month), but for some reason it is not calculating properly for September.

Formula:

=IF($I$8="Y",0,IF(E2="N",($D2/(INT((DAY(DATE(YEAR($I$5),MONTH($I$5)+1,1)-WEEKDAY(DATE(YEAR($I$5),MONTH($I$5)+1,2)))+6)/7)))*NETWORKDAYS.INTL(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY()+1,"1111101"),$D2))

I5=9/7/2018
I8="N"
E2="N"
D2=$114.88

The cell should result in displaying, as of today, $57.44. The reason is that every Friday the system should calculate how much money to set aside until it reaches the last Friday in the month, which will show the same amount in D2. August had 5 Fridays so the deduction was less for each Friday. Other months worked fine that contained 4 and 5 Fridays. Not sure why September is not working correctly. Currently displays $86.16.

TIA,
Jay

 

[Dave Patton]

check your formula "1111101"

 

[JayB0730]

That's the problem I'm facing, I can switch the formula "1111101" around to make it work this time but if I go back it would have messed up previously, which obviously means that it will result in an incorrect answer moving forward. I think the problem has to do with the number of days in the month, calculating the next Friday. I just can't figure out how to make this work.

 

[Dave Patton]

Your formula is calculating the number of Saturdays. I thought you wanted to calculate the number of Fridays!

 

[Dave Patton]

Excel 2010
	B	C	D	E
1	14-Sep-18			14-Sep-18
2			$114.88	N
3	57.44
8b

Cell Formulas
Range	Formula
B3	=D2/NETWORKDAYS.INTL(B1-DAY(B1)+1,EOMONTH(B1,0),"1111011")*NETWORKDAYS.INTL(B1-DAY(B1)+1,E1,"1111011")

 

[JayB0730]

Excel 2010

	B	C	D	E
N

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]14-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Sep-18[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$114.88 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]57.44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

8b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=D2/NETWORKDAYS.INTL(B1-DAY(B1)+1,EOMONTH(B1,0),"1111011")*NETWORKDAYS.INTL(B1-DAY(B1)+1,E1,"1111011")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I really appreciate the help. My needs are more dynamic but you got the smoke burning upstairs Here is the revised formula that works perfectly now and is more concise:

IF($I$8="Y",0,IF(E2="N",($D2/NETWORKDAYS.INTL(EOMONTH($I$5,-1)+1,EOMONTH($I$5,0),"1111011")*NETWORKDAYS.INTL(EOMONTH($I$5,-1)+1,TODAY(),"1111011")),$D2))

D2 = Total Amount allocated for month
E2 = Reset Flag
I5 = Any date within current month