Microsoft Office Tutorials and References

In Depth Information

**CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS**

Figure 5.
h

e 1s in column N mean the date is not a Sunday.

Finally,
SUMPRODUCT
multiplies the
Not Holiday
array by the
Not Sunday

array. When both arrays contain a 1, we have a workday. When either the
Not

Holiday
array has a 0 (as in row 8) or the
Not Sunday
array has a 0 (as in

row 7), the result is a 0. The ﬁ nal result is shown in the
SUM
function in P10:

There are 6 workdays between the two dates.

As with most array solutions, this one formula manages to do a large number

of sub-calculations to achieve a single result.

Additional Details:
What if you work 7 days a week but want to exclude

company holidays? The formula is simpler:

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0))

The problem becomes trickier if days in the middle of the week are the days

off. Say that you have a part-time employee who works Monday, Wednesday,

and Friday. The
Not Sunday
portion of the formula now needs to check for

3 speciﬁ c weekdays. Note that the
Return_Type 2
version of the
WEEKDAY

function never returns a 0. Because this version of the
WEEKDAY
function

returns digits 1 through 7, you can use it as the ﬁ rst argument in the
CHOOSE

function to specify which days are workdays. Using
=CHOOSE(WEEKDAY(Some

Date,2),1,0,1,0,1,0,0)
would be a way of assigning 1s to Monday,

Wednesday, and Friday.

Because
CHOOSE
does not usually return an array, you have to enter the

following formula, using Ctrl+Shift+Enter:

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A3&":"&B3)))=0),-

-(CHOOSE(WEEKDAY(ROW(INDIRECT(A3&":"&B3)),2),1,0,1,0,1,0,0)))