MrExcel.com - Your One Stop for Excel Tips & Solutions

Store Holidays in a Named Range

January 25, 2021 - by

Store Holidays 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:

  1. Type your company holidays as a column of dates in E1:E10.
  2. 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.
  3. Press Ctrl+C to copy the array to the Clipboard.
  4. Press Esc to exit Formula Edit mode. The formula disappears.
  5. Visit the Name dialog box. (In Excel 97-2003, select Insert, Name, Define. In Excel 2007, select Formulas, Define Name.)
  6. Type Holidays as the name.
  7. 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.
Figure 6. Press F9, and Excel converts the range reference to an array of serial numbers.

Now you can use the named range Holidays as the third argument of the WORKDAY and NETWORKDAYS functions.

Gotcha: While these names work fine with WORKDAY and NETWORKDAYS, they fail in complex array formulas.

Summary: You can convert a range of dates to a named array to simplify the use of the WORKDAY and NETWORKDAYS functions.

Title Photo: Brooke Lark at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.