NETWORKDAYS formula error (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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 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.

Reply | Quote

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

Reply | Quote

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

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

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:

=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)))))

Reply | Quote

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.

Reply | Quote