1. ## NETWORKDAYS (2000)

I have a spread sheet with this formula in it "=NETWORKDAYS(E16,H16,holidays)" we use it to track the average time it takes an employee to complete certain assignments. It works fine except for one problem. If an employee finishes an assignment the same day he gets it it returns a "1" If he does it the next day it returns a "2". Obviously this is misleading. I want it it to return a "0" if it is completed the same day. I can change the formula to read, "=NETWORKDAYS(E16,H16,holidays)-1". But then it returns a -1 for blank lines and that screws up the average which is calculated by summing all the values in networkdays column and dividing the sum by the number of Non -blank cells in Column B.
Can some one suggest a solution for this problem?

2. ## Re: NETWORKDAYS (2000)

Try the formula below:

<pre>=IF(AND(E16<>"",H16<>""),NETWORKDAYS(E16,H16, holidays)-1,"")
</pre>

If you really want a zero result when either E16 or H16 is empty, then use:

<pre>=IF(AND(E16<>"",H16<>""),NETWORKDAYS(E16,H16, holidays)-1,0)
</pre>

However this is technically not correct since 0 means that the start and end day were the same. Also, if you use the first formula, you should be able to use the following to get the aveage:

<pre>=AVERAGE(I16:I179)
</pre>

3. ## Re: NETWORKDAYS (2000)

=IF(E16*H16,NETWORKDAYS(E16+1,H16,holidays),)

4. ## Re: NETWORKDAYS (2000)

For your amusement, I track my team's project turnarounds using zero as the result for a same day turnaround. Helps the stats look good, and gives credit for extra effort for doing a same-day. (However I don't adjust for ease of project. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>)

5. ## Re: NETWORKDAYS (2000)

Makes sense to me. If the work load is distributed fairly everyone gets the same number of easy ones.

6. ## Re: NETWORKDAYS (2000)

The condition for the IF function is "E16*H16". How does Excel evaluate that as true or false?

7. ## Re: NETWORKDAYS (2000)

In Excel any non-zero value is evaluated as TRUE. So if either Start Date or End Date cell is zero or empty, E16*H16 evaluates to FALSE. If there is text in either cell, the formula will error out; Legare's formula is safer in that regard.

8. ## Re: NETWORKDAYS (2000)

Thanks, I learned something new. I always thought the condition had to be an equation or an "ineaquation". is there such a word? If not there should be. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

9. ## Re: NETWORKDAYS (2000)

Nope. A simpler example is that

=IF(cell<>0,TRUE_RETURN,FALSE_RETURN)

can be written

=IF(cell,TRUE_RETURN,FALSE_RETURN)

Also, an easy way to avoid divide by zero errors is:

=IF(denominator,numerator/denominator,)

provided that a return of zero is acceptable. Note that in the last one I also use the shortcut of leaving the FALSE_RETURN (last bit of the IF after the second comma) blank to return a zero.

What can I say, I'm a terrible typist, so I use the most concise expressions I can find. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•