Microsoft Office Tutorials and References
In Depth Information
STORE HOLIDAYS IN A NAMED RANGE
Summary: This topic introduces the concept of creating a huge array from two
simple values. For example, =ROW(INDIRECT("1:10000")) generates a
10,000-cell array ﬁ lled with the numbers from 1 to 10,000. You can use this
concept to test many dates while only specifying a starting and ending point,
thus solving the NETWORKDAYS problem for any type of workweek.
IN A NAMED RANGE
Challenge: The NETWORKDAYS and WORKDAY functions can take a list of
company holidays as the third argument. If you store the list of holidays in AZ1:
AZ10, there is a chance that someone will inadvertently delete a row, so you
want to move the range of company holidays to a named range.
Solution: There is an easy way to convert the range of holidays to a named
range. Follow these steps:
Type your company holidays as a column of dates in E1:E10.
In a blank cell, type =E1:E10 . Do not press Enter. Instead, press the F9 key.
Excel calculates the formula and returns an array of date serial numbers,
as shown in Figure 6. Notice that everything after the equals sign is already
Press Ctrl+C to copy the array to the Clipboard.
Press Esc to exit Formula Edit mode. The formula disappears.
Visit the Name dialog box. (In Excel 97-2003, select Insert, Name, Deﬁ ne.
In Excel 2007, select Formulas, Deﬁ ne Name.)
Type Holidays as the name.
In the Refers To box, clear the current text. Type an equals sign. Press
Ctrl+V to paste the array of dates to the box. Click OK.
Figure 6. Press F9, and Excel converts the range reference to an array of serial numbers.