Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NETWORKDAYS formula error (Excel 2000)

    I'm having a weird error in my Excel table when using the NETWORKDAYS formula. I will have two dates, for example, 10/1/2004 in both the A column and B column, where the formula is trying to show the difference between the two dates. However, in one calcuclation it will say 0 days between the two dates, and others it will say one day. I want it to show 0 days, so I added a -1 in the formula to make it that 10/1 - 10/1 = 0 days, but it's not doing that in every calculation.

    I hope I've made sense. Please look at the examples on the attached spreadsheet. The ones that are highlighted in yellow are not calculating correctly.

    Also, am I doing the "holidays" part of this formula right, with connecting it to the dates in the second worksheet?

    Thank you all so much for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: NETWORKDAYS formula error (Excel 2000)

    NETWORKDAYS includes both the start date and the end date in the count, but it only counts working days (Monday through Friday); weekend days and holidays are not counted. So NETWORKDAYS(MyDate, MyDate) returns 1 if MyDate is a working day, but 0 if MyDate is a weekend day or holiday. How do you want to treat weekend days?

    BTW, you treated the Holidays argument correctly.

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NETWORKDAYS formula error (Excel 2000)

    Ok, that makes total sense. To answer your question, I need the formula to show 0 as a result any time that the first date and the second date are the same, regardless if they are a weekday or a weekend. In other words, if the customer request date (Column [img]/forums/images/smilies/cool.gif[/img] is the same as our scheduled delivery date (Column C), then we were able to meet the customer's objectives which should reflect as a 0 on the report. What formula modifications do I need to make? Or should I use a different formula?

    Also, I have one If-Then formula in cell G17 of my attached document. I need to add a second If Then statement in the formula for instances such as this one where there is no request date. Here is the longhand version of what I need the formula to do:

    If the request date is null, then "No Req Date "[NOTE: Can I include a space here before the quotes, in case both sets of text end up in the formula?]; also if the Date Shipped is null, then "Not Shipped"; otherwise NETWORKDAYS (D,B,Holidays!$A$4:$A$!!)

    I'm not sure if there will be any instances where there is no Request Date and no Ship Date, but I need it to have both of those default texts in there for both instances.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: NETWORKDAYS formula error (Excel 2000)

    How about this:
    =IF(B2="","No Req Date",IF(D2="","Not Shipped",IF(B2=C2,0,NETWORKDAYS(D2,B2,Holidays!$A$ 4:$A$11))))

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: NETWORKDAYS formula error (Excel 2000)

    Steve already replied your first question. To take a blank request date into account, you can use this formula in G2 (and fill down):

    <code>=IF(B2="","No Req Date",IF(D2="","Not Shipped",IF(B2=D2,0,NETWORKDAYS(B2,D2,Holidays!$A$ 4:$A$11))))</code>

    Because of the nested IFs, G2 will display "No Req Date" if B2 is blank, and "Not shipped" if B2 is not blank, but D2 is. If you really want to see both texts if both cells are blank, you need to check that situation too:

    <code>=IF(B2&D2="","No Req date and Not Shipped",IF(B2="","No Req Date",IF(D2="","Not Shipped",IF(B2=D2,0,NETWORKDAYS(B2,D2,Holidays!$A$ 4:$A$11)))))</code>

  6. #6
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NETWORKDAYS formula error (Excel 2000)

    Thanks Steve and Hans! That formula is working great! I don't use if-then formulas often and always get confused about the protocols when I start doing multiples If's in a formula. I really appreciate the help. [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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