Getting Networkdays to accurately reflect the number of days between two dates

Question

I am using the NETWORKDAYS formula to count days between 2 days for a series of different transaction dates. I want the count to exclude weekends and holidays on each transaction date. However, the result doesn't come out as I expected.

For example, the transaction between 1/26 and 1/27 should have been 1 day, but it is counted as 2 days. Is there any easy way to count the days? I have tried to use a simple formula such as =C9-C8, which would give me 1 day. Because I have a large amount of data to work with, it would be very difficult use that approach and excluding weekends and holidays.

=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)

Example:

Date on Column C    Result on Column F   Row 7: 1/20/10 12:00 AM           
Row 8: 1/26/10 12:00 AM          5       Row 9: 1/27/10 12:00 AM             2
Row10: 1/28/10 12:00 AM          2

Answer 1

I think it is because it includes both the start and the end date in the calculation. So I think you are really close, and just need take your count and minus 1 out of it. Something like this -

=(NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)) - 1

Does this make sense?

Answer 2

This is an old question that has continued to gather answers over the years. The existing answers can produce a correct result in some cases, but the issue isn't quite as simple as these answers describe.

NETWORKDAYS gives you the number of workdays contained in a period, including the first and last days. In some situations, that isn't really the measure you need, so the formula needs adjustment. However, there are many different scenarios, both for how you might need it adjusted, and when the start and end dates fall relative to excluded days. There isn't actually a single adjusted formula that gives the right answer for every case. I'll lay out the tools to produce the adjusted result you need for your specific case.

If the objective is not the number of workdays contained in a date range, here are some of the variations that could need to be handled:

• Start and end on the same day. NETWORKDAYS produces a result of 1 if that day is a workday or 0 if it is a non-workday. That would typically be the desired result, but some variations would say that if it is a workday, the result should be either 0 or a fraction of a day in that case (see next bullet).

• For work planning, the start and end days can have special meanings. Work can be assigned or initiated on the start day, but that day isn't considered a resource for accomplishing the task. In that case, the start day is subtracted from the pool of resource days.

  On the other hand, the end date can be viewed as a completion date, with results due early in the day. In that case, the end date is subtracted from the pool.

  Still another scenario considers "average days". Work on a task begins sometime during the start day and is completed sometime during the end day. So a task that occurs over Monday and Tuesday is viewed as half a day on Monday and half a day on Tuesday, for a total resource of one day. The adjustment would be subtracting half a day from both the start and end dates.

• All of that depends, however, on whether the start and end dates are workdays. If a period starts on a Sunday and ends on a Saturday, a full week of workdays is available. There would be no adjustment.

  If the period starts or ends on a non-workday and the other end of the period is a workday, the adjustment would depend on how the start and end days are considered (see previous bullet).

So if you want to use NETWORKDAYS for a purpose other than how it was intended, you need to build a formula that correctly reflects how you want to view start and end days for your purpose. You can simplify that task by using NETWORKDAYS again for the adjustments.

If you use either the start or end date to represent a date range, NETWORKDAYS will return 0 if that day is a non-workday, or 1 if it is a workday. For example:

NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)

will return a 0 or 1 value for the start date. You can use that to build an adjustment that automatically adjusts for whether that date is a workday. Examples:

• Exclude start date if it is a workday:

  =NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)
  

• Exclude end date if it is a workday:

  =NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-NETWORKDAYS(C9,C9,holiday!$A$106:$A$117)
  

• Exclude half a day from both start and end dates if they are work days:

  =NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-(NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)+NETWORKDAYS(C9,C9,holiday!$A$106:$A$117))/2
  

  Note that if one of either the first or last day is a non-workday, you will end up with a value containing half a day. For that scenario, if you are interested only in whole days, you would need to add rounding in the desired direction.

If you can have periods of a single day, you need to decide how to handle that within your rules; it may be an exception to how you define workdays. If you always want to consider that to be one full day, you could build in an IF test to see if the NETWORKDAYS result is 1, and assign a result of 1 instead of your adjustment.

If you can have periods of a single day and that could potentially fall on a non-workday, you could use an IF test to check for NETWORKDAYS equal 0, in which case, you could return a text warning or trigger an error condition.

Note that NETWORKDAYS uses only the date (integer) portion of date/time values; any time is ignored. One ramification is that half-day adjustments, like in the last example, aren't affected by any time value, and time values can't be used to create half-day adjustments.

Answer 3

Networkdays does not account for saturdays and sundays e.g. start day: Friday End-day: Monday (networkdays-1 will result in '1' which is precisely what you want) but when start-day: Satday or Sunday End-day: Monday (networkdays-1 will result in '0' which is not the correct answer) we have to adjust for these weekends by ourselves using weekday function.

Answer 4

As above, I agree the issue is that both start and end date are counted, I think the answer is not to count your end date.

Rather than deducting 1 from the end result (which would mess up the end result in some instances), you want to deduct 1 from your end date.

So if your start date is C8 and your end date is C9, you want

=NETWORKDAYS(C8,(C9-1),holiday!$A$106:$A$117)

Simple, and it works for me at least!

Answer 5

NETWORKDAYS counts both days, since both are working days. Think of it as a "how many working days are between 1/26 00:00 am and 1/27 00:00 am" function.

In order to achieve what you want, you could mix that up with an IF function.

IF(NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)>1;NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-1;1)

There might be a more elegant way to do it, though.

Answer 6

I needed something similar and used the formula below. You might be able to adapt it to what you need.

=IF(NETWORKDAYS((C2+1),D2,Holidays!A1:A8)<0,0,NETWORKDAYS((C2+1),D2,Holidays!A1:A8))