Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: NETWORKDAYS (2000)

    =IF(E16*H16,NETWORKDAYS(E16+1,H16,holidays),)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NETWORKDAYS (2000)

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

  6. #6
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NETWORKDAYS (2000)

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

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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