Page 128 - Excel Workbook for Dummies

12_798452 ch07.qxp 3/13/06 7:51 PM Page 111
Chapter 7: Creating Date and Time Formulas 111
WORKDAY(start_date,days,[holidays]) calculates the work date that is so
many days ahead (positive days argument) or behind (negative days argument)
the start_date argument you specify — the optional holidays argument can spec-
ify a range of holiday dates to be excluded from the calculation.
YEARFRAC(start_date,end_date,[basis]) calculates the fraction of the year
between the start_date and end_date arguments you specify — the optional
basis argument can be a number between 0 and 4 that signifies the following:
• 0 (or no basis argument) bases the year fraction on the U.S. (NASD)
method of 30/360 (whereby if the starting date is equal to the 31st of the
month, it becomes equal to the 30th and if the ending date is equal to the
31st of the month, the ending date becomes the 1st of the following
month).
• 1 bases the year fraction on the actual days divided by the actual days.
• 2 bases the year fraction on the actual days divided by 360.
• 3 bases the year fraction on the actual day divided by 365.
• 4 bases the year fraction on the European method of 30/360 (whereby start-
ing and ending dates that fall on the 31st of the month are made equal to
the 30th of the same month).

Try It

Exercise 7-3: Building Formulas with Date Functions in the Analysis ToolPak
Use the Solved7-2-mine.xls workbook file you created when doing the previous exer-
cise. If you did not perform this exercise or no longer have access to this workbook,
open the Solved7-2.xls file located in your Chapter 7 folder in the My Practice
Spreadsheets folder on your hard disk or on the Excel Workbook CD-ROM. In this
exercise, you activate the Analysis ToolPak add-in and then practice using its
WEEKNUM and NETWORKDAYS functions:

1. Position the cell cursor in cell C10 and then select the Analysis ToolPak check
box in the Add-Ins dialog box, if this check box is not already selected in your
copy of Excel (Tools➪Add-Ins).
If you’ve not previously installed this add-in, Excel displays an alert dialog box
asking you if you want to install this add-in.
2. Select Yes in the alert dialog box asking you to install this add-in if prompted to
do so.

3. Select Date & Time as the category in the Or Select a Category drop-down list
box in the Insert Function dialog box (Insert➪Function) to display the list of Date
and Time functions.
Scroll through the list of Date & Time functions verifying that your activation of
the Analysis ToolPak add-in has inserted the EDATE, EOMONTH, NETWORKDAYS,
WEEKNUM, and YEARFRAC functions.
4. Select the WEEKNUM function in the Select a Function list box before you choose
OK to close the Insert Function dialog box.
5. Select cell B10 for the Serial_number argument and choose OK to close the
Function Arguments dialog box.
6. Copy the WEEKNUM formula in cell C10 down to the cell range C11:C14.