Page 127 - Excel Workbook for Dummies

12_798452 ch07.qxp 3/13/06 7:51 PM Page 110
110 Part II: Using Formulas and Functions

3. In cell D3, create a date entry using the DATE function with the three separate
numeric entries in cells A3, B3, and C3 as its arguments in the following order:
• C3 as Year argument
• A3 as Month argument

• B3 as the Day argument
4. Format the date returned by the DATE function in cell D3 with the March 14, 2001,
date format and then copy this DATE formula down to the cell range D4:D8.
Note that contents of the cell range D3:D8 is composed of DATE function formu-
las rather than date serial numbers. If you want to convert the formulas to their
calculated values, you need to use the Values option in the Paste Special dialog
box.
5. Select the cell range D3:D8 and then copy it to the Clipboard (Ctrl+C).
6. Position the cell pointer in E3 and then select the Values option button in the
Paste Special dialog box.
Excel copies the date serial numbers into the range E3:E8. Note that the date
serial numbers copied into this cell range are completely static: Unlike the DATE
function formulas in the cell range D3:D8, revising the numeric entries made in
the cells in the range A3:C8 would have no effect on them.
7. Use the DATEVALUE function in cell B10 to convert the text entry in cell A10 to
the date serial number and then copy the formula down to the cell range
C11:C14.
8. Position the cell cursor in cell B1 of the Date Formulas sheet and then save a
copy of the workbook with the filename Solved7-2-mine.xls in your Chapter 7
folder in the My Practice Spreadsheets folder, but leave the workbook open for
Exercise 7-3.

Add-in programs extend Excel’s basic feature set, usually by giving you access to new,
specialized functions. Excel ships with several add-in programs including the Analysis
ToolPak with its extra Date functions. Before you can use the functions or features
offered by an add-in program, you must activate it by selecting its check box in the
Add-Ins dialog box (Tools➪Add-Ins).

The Analysis ToolPak, which you activate in the next exercise, adds the following
Date functions to Excel:

EDATE(start_date,months) calculates the elapsed date so many months ahead
(positive months argument) or behind (negative months argument) the start_
date argument you specify.
EOMONTH(start_date,months) calculates the last day of the month so many
months ahead (positive months argument) or behind (negative months argu-
ment) the start_date argument you specify.
NEWORKDAYS(start_date.end_date,[holidays]) calculates the number of work
days between the start_date and end_date arguments — the optional holidays
argument can specify a range of holiday dates to be excluded from the workday
total.
WEEKNUM(serial_number,[return_type]) calculates a number indicating where
the week in the date specified by the serial_number argument falls within the
year — the optional return_type argument can be the number 1 or 2, where 1 (or
no return_type argument specified) starts the week on Sunday (1) and ends it on
Saturday (7) and 2 starts the week on Monday (1) and ends it on Sunday (7).