Network Days Calculations.

[nparsons75]

Hi,

I am struggling to get this to work, hopefully someone can help...

In the table below I have dates (E) and results (H). During the month E5 auto updates with the current date. Based on this the Results change on a daily basis. This seems to work fine all through the month until the last day.

To simulate, change E5 to the 15/05/21 and the results will amend. They will show various details around days passed / left in the month.

At the end of the month when all days have passed I need (H8) and (H11) to read zero (0) as there are no days left...

If I select 1/07/21 in (E5) then the result in (H8, H11) should be zero (0), however, I get -2 ?

This sheet is being used in a wider document but the main functions are here.

Appreciate any help...

Book1
	D	E	F	G	H
4	FIRST DAY OF MONTH	01/06/2021
5	TODAYS DATE	01/07/2021
6	LAST DAY OF MONTH	30/06/2021		Days in Month	30
7				Total Days Elapsed	30
8				Total Days Left (inc. weekends)	-2
9				Working Days Available	22
10				Working Days Elapsed	22
11				Working Days Left	-2
12				Weekend Days + Holidays	8
13				% of Month Passed	1
Sheet1

Cell Formulas
Range	Formula
H6	H6	=IFERROR(DAY(EOMONTH(E4,0)),"")
H7	H7	=IFERROR(E5-E4,"")
H8	H8	=IFERROR(NETWORKDAYS.INTL(E5,E6,"0000000",holidays),"")
H9	H9	=IFERROR(NETWORKDAYS(E4,EOMONTH(E4,0),holidays),"")
H10	H10	=IFERROR(NETWORKDAYS(E4,E5,holidays)-1,"")
H11	H11	=IFERROR(NETWORKDAYS(E5,E6,holidays),"")
H12	H12	=IFERROR(H6-H9,"")
H13	H13	=H10/H9

Named Ranges
Name	Refers To	Cells
holidays	=holidays!$B$6:$B$27	H8:H11

 

[Fluff]

For H11 try

=IF(E5>E6,0,NETWORKDAYS(E5,E6,holidays))

and similar for H8

 

[Tetra201]

Or

=MAX(0,NETWORKDAYS(E5,E6,holidays))

 

[nparsons75]

For H11 try

=IF(E5>E6,0,NETWORKDAYS(E5,E6,holidays))

and similar for H8

Yep, that seems to have done the trick!! Any ideas on what is needed for H8?

 

[Fluff]

Just replace the final part of that formula with your formula.

 

[nparsons75]

Just replace the final part of that formula with your formula.

Apologies fluff, not sure which bit you mean?

 

[Fluff]

Just replace the part in red with your H8 formula

=IF(E5>E6,0,NETWORKDAYS(E5,E6,holidays))