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
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.