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.

Part

I

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)

Result:
{1;1;1;1;1;1;0;1}