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
MATCH function.
CALCULATE WORKDAYS
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:
=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A3&":"&B3)))
=0),--(WEEKDAY(ROW(INDIRECT(A3&":"&B3)),3)<6))

Search JabSto ::

Custom Search