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.

Source:
http://www.mrexcel.com/forum/showthread.php?t=69761

STORE HOLIDAYS

IN A NAMED RANGE

Part

I

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

selected.

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.

1.

2.

3.

4.

5.

6.

7.

Figure 6.
Press F9, and Excel converts the range reference to an array of serial numbers.