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.

Source:
http://www.mrexcel.com/forum/showthread.php?t=53223

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