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:
will return a
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:
Exclude end date if it is a workday:
Exclude half a day from both start and end dates if they are work days:
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.