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 fi 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 specifi 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 fi 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)))
 
Search JabSto ::




Custom Search