Microsoft Office Tutorials and References
In Depth Information
CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS
The second half of the formula uses the WEEKDAY function to ﬁ nd which dates
are not Sundays. The WEEKDAY function can return three different sets of
results, depending on the value passed as the Return_Type argument. Figure
4 show the values returned for various Return_Type arguments. In order
to isolate Monday through Saturday, you could check to see if the WEEKDAY
function with a Return_Type of 1 is greater than 1. You could check to see
if the WEEKDAY function with a Return_Type of 2 is less than 7. You could
check to see if the WEEKDAY function with a Return_Type of 3 is less than 6.
All these methods are equivalent.
Figure 4. h e WEEKDAY function can return 1, 7, or 6 for Sundays.
The second half of the formula uses many of the tricks from the ﬁ rst half. The
INDIRECT function returns a range of rows. The ROW function converts those
rows to row numbers that happen to correspond to the range of dates. The
WEEKDAY(,3) function then converts those dates to values from 0 to 6, where
6 is equivalent to Sunday. The virtual result of the WEEKDAY function is shown
in column L of Figure 5. The formula compares the WEEKDAY result to see if
it is less than 6. This virtual result is shown in column M of Figure 5. Finally,
a double minus converts the TRUE/FALSE values to 0s and 1s, as shown
in column N. Basically, this says that we are working every day in the range,
except for N7, which is a Sunday.
Formula fragment: --(WEEKDAY(ROW(INDIRECT(A3&":"&B3)),3)<6)