Microsoft Office Tutorials and References

In Depth Information

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

Now you can compare the holiday list to the range of dates.

=COUNTIF(Holidays,sub-result)
counts how many times each holiday

is in the range of dates. In this case, you expect the function to return a 1 if

a holiday is found in the range of dates and a 0 if the holiday is not found.

Because you want to count only the non-holiday dates, the formula compares

the
COUNTIF
result to ﬁ nd the dates where the holiday
COUNTIF
is 0:

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

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

For every date in the date range, the
COUNTIF
formula asks, “Are any of the

company holidays equal to this particular date?” Figure 3 illustrates what is

happening in the ﬁ rst half of the formula. Column E represents the values

returned by the
ROW
function. Column F uses
COUNTIF
to see if any of the

company holidays are equal to the value in column E. For example, in E3,

none of the holidays are equal to 39855, so
COUNTIF
returns 0. However,

in F8, the formula ﬁ nds that one company holiday is equivalent to 39860, so

COUNTIF
returns 1.

In column G, you test whether the result of the
COUNTIF
is 1. If it is, the
TRUE

says to count this day.

In column H, the minus-minus formula converts each
TRUE
value in column G

to 1 and each FALSE value in column G to 0.

In Figure 3, cells H2:H9 represent the virtual results of the ﬁ rst half of the

formula, which ﬁ nds the dates that are not holidays.

Figure 3.
h

e i rst half of the formula counts days that are not holidays.