4

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
5

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?

3
  • It works perfectly for what I need. Thanks a lot!
    – user56517
    Nov 23 '10 at 17:37
  • You mind marking this as the answer since it sounds like it fixed your problem? Thanks.
    – Ben Jones
    May 31 '11 at 14:05
  • 2
    This can give the wrong answer if the start and/or end date is on a weekend or holiday.
    – fixer1234
    Feb 13 '19 at 0:30
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.

1
  • What about subtracting 1 IFF both start and end date are workdays? =NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-(NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)*NETWORKDAYS(C9,C9,holiday!$A$106:$A$117)) Sep 23 '20 at 18:51
0

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.

1
  • This gives the wrong answer if the start and/or end date is an excluded day.
    – fixer1234
    Feb 13 '19 at 0:36
0

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.

2
  • I don't have Excel 2007 but that's not the way it works in other versions of Excel, plus even if it does work that way in 2007, this isn't an answer, it's a best a comment.
    – blm
    Jan 17 '16 at 18:41
  • Actually, what this answer refers to is part of the issue. The answer simply wasn't fleshed out into a solution.
    – fixer1234
    Feb 13 '19 at 0:39
0

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!

1
  • No, this gives you the wrong date, which could be an excluded date and produce a different error.
    – fixer1234
    Feb 13 '19 at 0:33
0

To solve this I used an IF Statement that subtracted 1 if the value is positive and added one if the value is negative.

My comparison dates were in columns AM & AL:

=IF(((NETWORKDAYS.INTL(AL2,AM2))-1)<0,NETWORKDAYS.INTL(AL2,AM2)+1,NETWORKDAYS(AL2,AM2)-1)

I wasn't calculating in holidays for this metric (wasn't necessary) and it gave me the correct answer.

0

=IF(AL2=N2,0,IF(AL2>N2,NETWORKDAYS(N2,AL2,holidays)-1,IF(AL2<N2,NETWORKDAYS(N2,AL2,holidays)+1)))

1
  • 2
    Welcome to Super User! While this may answer the question, it would be a better answer if you could provide some explanation why it does so.
    – DavidPostill
    Sep 23 '20 at 18:16
0

I've found a simple solution that doesn't use complex if statements or other. The trick is to include the start date in the holiday input. The issue I was facing was the following:

  • Start date 10/14/2020, end date 10/13/2020 - Result -2 (I want it to show -1)
  • Start date 10/14/2020, end date 10/14/2020 - Result 1 (I want it to show 0)
  • Start date 10/14/2020, end date 10/15/2020 - Result 2 (I want it to show 1)

So there is a 3 day jump...I worked around by adding start date in the holidays so the formula example would be =NETWORKDAYS(A3,B3,A3)

I know this is old, but the other solutions don't seem very direct and get too convoluted. Hope this helps someone.

-1

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

1
  • While this may answer the question, providing additional context regarding why and/or how it answers the question improves its long-term value. Mar 10 '17 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.