Microsoft Office Tutorials and References
In Depth Information
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 fi 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 fi 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 fi 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}
Search JabSto ::

Custom Search