Microsoft Office Tutorials and References

In Depth Information

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

Although this formula deals with an array of cells, it ultimately returns a single

value, so you do not need to use Ctrl+Shift+Enter when entering this formula.

Breaking It Down:
The formula seeks to check two things. First, it checks

whether any of the days within the date range are in the holiday list. Second,

it checks to see which of the dates in the date range are Monday-through-

Saturday dates.

You need a quick way to compare every date from A3 to B3 to the holiday list.

In the current example, this encompasses only 8 days, but down in row 5, you

have more than 300 days.

The formula makes use of the fact that an Excel date is stored as a serial

number. Although cell A3 displays February 10, 2009, Excel actually stores the

date as 39854. (To prove this to yourself, press Ctrl+` to enter Show Formulas

mode. Press Ctrl+` to return to Normal mode.)

It is convenient that Excel dates in the modern era are in the 39,000–41,000

range, well within the 65,536 rows available in Excel 97-2003. The date

corresponding to 65,536 is June 5, 2079, so this formula will easily continue

to work for the next 70 years. (And if you haven’t upgraded to Excel 2007 by

2079, well, you have a tenacious IT department.)

Excel starts evaluating this formula with the ﬁ rst
INDIRECT
function. The

arguments inside
INDIRECT
build an address that concatenates the serial

number for the date in A3 with the serial number for the date in B3. As you can see

in the sub-result, you end up with a range that points to rows 39854:39861:

Formula fragment:
INDIRECT(A3&":"&B3)

Sub-result:
INDIRECT("39854:39861")

Normally, you would see something like
"A2:IU2"
as the argument for

INDIRECT
. However, if you have ever used the
POINT
method of entering

a formula and gone from column A to the last column, you will recognize that

=SUM(2:2)
is equivalent to
=SUM(A2:IV2)
in Excel 2003 and
=SUM(A2:

XFD2)
in Excel 2007.

The ﬁ rst step of the formula is to build a reference that is one row tall for each

date between the start and end dates.

Next, the formula returns the
ROW
function for each row in that range. In the

case of the dates in A3 and A4, the formula returns an array of eight row

numbers (in this case,
{39854;39855;39856;…;39861})
. This is a clever

way of returning the numbers from the ﬁ rst date to the last date. In row 5, the

ROW
function returns an array of 364 numbers:

Formula fragment:
ROW(INDIRECT(A3&":"&B3))

Sub-result:
{39854;39855;39856;39857;39858;39859;39860;39861}

Part

I