Page 129 - Excel Workbook for Dummies

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

7. Switch to the 2006 Holidays worksheet and then use the individual calendar
worksheets (Jan-06 through Dec-06) to enter the dates of the holidays missing in
the cell range B3:B13.
Martin Luther King Day is in January
Presidents’ Day is in February
Memorial Day is in May
Labor Day is in September
Veteran’s Day is in November as is Thanksgiving in the U.S.A.
8. Name the cell range B3:B13 Holidays_06 and then position the cell cursor in
cell D3.
9. Construct a formula in this cell using the NETWORKDAYS function that calcu-
lates the number of workdays between 12-31-05 and 12-31-06.
Select cell B15 in Start_date text box and cell B16 in the End_date text box in the
Function Arguments dialog box
10. Construct a formula in cell D4 using the NETWORKDAYS function that calculates
the number of workdays between 12-31-05 and 12-31-06 and that deducts the hol-
idays that fall on workdays in the named range Holidays_06 in the 2006 Holidays
worksheet.
When creating this NETWORKDAYS function, type the range name, Holidays_06,
in the Holidays text box in the Function Arguments dialog box after selecting cell
B15 in the Start_date text box and B16 in the End_date text box.

11. Check your answers against those shown in the 2006 Holidays sheet in the
Solved7-3.xls workbook file in your Chapter 7 folder inside the My Practice
Spreadsheets folder or on your Excel Workbook CD-ROM. When everything
checks out, save your workbook in the Chapter 7 folder with the filename
Solved7-3-mine.xls.

Working with the Time Functions

Excel’s Time functions are a smaller subset of the Date functions. They include the
NOW function, which returns the current date and time; TIME, which creates a time
fraction from values representing the hour, minutes, and seconds; and TIMEVALUE,
which converts a time entered or imported into the worksheet as text so that you can
use it in formulas that calculate elapsed times.

Try It

Exercise 7-4: Building Formulas with Common Time Functions
Open the Exercise7-4.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. The file
contains a Time Formulas worksheet that you can use to practice using some of the
Time functions:

1. Display the current time in cell B1 using the NOW function — format the result
with the 1:30 PM Time format.
2. In cell D3, use the TIME function to enter a time using the entry in cell A3 as its
Hour argument, B3 as its Minute argument, and C3 as its Second argument.