Microsoft Office Tutorials and References
In Depth Information
CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS
Summary: The formula to return the ﬁ rst non-blank cell in a row starts with
a simple ISBLANK function. Using INDEX to coax the string of results into an
array allows this portion of the formula to be used as the lookup array of the
FOR 5-, 6-, AND 7-DAY WORKWEEKS
Challenge: Calculate how many workdays fall between two dates. Excel’s
NETWORKDAYS function does this if you happen to work the ﬁ ve days between
Monday and Friday inclusive. This topic will show you how to perform the
calculation for a company that works 5, 6, or 7 days a week.
Background: The NETWORKDAYS function calculates the number of workdays
between two dates, inclusive of the beginning and ending dates. You specify
the earlier date as the ﬁ rst argument, the later date as the second argument,
and optionally an array of holidays as the third argument. In Figure 2, cell C3
calculates only 5 workdays because February 16, 2009, is a holiday. This is a
cool function, but if you happen to work Monday through Saturday, it will not
calculate correctly for you.
Figure 2. Traditionally, NETWORKDAYS assumes a Monday–through-Friday workweek.
Setup: Deﬁ ne a range named Holidays to refer to the range of holidays.
Solution: The formula in C3 is: