Networkdays

[Guest]

I am in the last stretch of my project for work. I am hung up on one tiny detail. I couldn't figure out how to upload my spreadsheet so I'll have to explain. I am currently using this formula: =IF(E3=0,"",NETWORKDAYS(B3,E3,Holiday))
to calculate the work days that pass between the beginning of a project and the end of the project. The start day, however, can not be counted in the total calculation.

I need help with a formula that will return these results:

Start Finish Total Days

12/8 12/11 3
12/8 Shouldn't calculate because there is no data in "Finish"
12/8 12/8 1
12/8 12/9 1

The problem I am running into is that 12/8 - 12/11 is calculated as 4 days. If I just subtract 1 from the formula than I get inaccurate data in the 12/8-12/8 and 12/8-12/9 scenarios. The importance of accurately returned data is I have another formula calculating the average values returned in "Total Days".
I tried this formula:
=IF(E13=0,"",NETWORKDAYS(B9,E9-1,Holiday)) and this version
=IF(E13=0,"",NETWORKDAYS(B9,E9,'Holiday )-1)

and then I tried a version of the above with another "if" saying that if "Start" and "Finish" equal each other than "Total Days" =1.

All of these versions somehow mess up my Average formula. =AVERAGE(IF(F3:F562>0,F3:F562))
The Average formula was written so that 0 values aren't calculated in the average. I had to do it this way because despite unchecking the "Show Zero Values" box, Excel still sees the 0 value in the entire range of cells.

Please help

Matt

 

[Vasant Nanavati]

Not tested, but have you tried:

=NETWORKDAYS(B9+1,E9,Holiday)

?

--

Vasant

I am in the last stretch of my project for work. I am hung up on one tiny

detail. I couldn't figure out how to upload my spreadsheet so I'll have to
explain. I am currently using this formula:
=IF(E3=0,"",NETWORKDAYS(B3,E3,Holiday))

to calculate the work days that pass between the beginning of a project

and the end of the project. The start day, however, can not be counted in
the total calculation.

I need help with a formula that will return these results:

Start Finish Total Days

12/8 12/11 3
12/8 Shouldn't calculate because there is no data in "Finish"
12/8 12/8 1
12/8 12/9 1

The problem I am running into is that 12/8 - 12/11 is calculated as 4

days. If I just subtract 1 from the formula than I get inaccurate data in
the 12/8-12/8 and 12/8-12/9 scenarios. The importance of accurately
returned data is I have another formula calculating the average values
returned in "Total Days".

I tried this formula:
=IF(E13=0,"",NETWORKDAYS(B9,E9-1,Holiday)) and this version
=IF(E13=0,"",NETWORKDAYS(B9,E9,'Holiday )-1)

and then I tried a version of the above with another "if" saying that if

"Start" and "Finish" equal each other than "Total Days" =1.

All of these versions somehow mess up my Average formula. =AVERAGE(IF(F3:F562>0,F3:F562))
The Average formula was written so that 0 values aren't calculated in the

average. I had to do it this way because despite unchecking the "Show Zero
Values" box, Excel still sees the 0 value in the entire range of cells.