Page 126 - Excel Workbook for Dummies

12_798452 ch07.qxp 3/13/06 7:51 PM Page 109
Chapter 7: Creating Date and Time Formulas 109

10. Convert the fraction of the day into hours by editing the formula in cell C6 so
that the program multiplies the difference between the two cells by 24.
When editing the formula, be sure to enclose the subtraction operation in paren-
theses so that Excel performs the subtraction first and then multiplies this differ-
ence by 24 as in
=(B6-A6)*24
11. Enter the following times in the designated cells:
• 7:35 AM in cell A7
• 15:05 in cell B7
• 9:45 in cell A8

• 6 PM in cell B8
12. Use the Fill handle to copy the formula in cell C6 down to the range C7:C8.
Because you edited the basic formula in cell C6 so that it returns the number
of hours elapsed (rather than the fractional part of the day) and formatted the
result with the Number format with two decimal places before you copied the
formula, the copies in cells C7 and C8 automatically return the number of hours
expressed with two decimal places.
13. Position the cell cursor in cell A1 and then save this new workbook with the file-
name Solved7-1.xls in your Chapter 7 folder in the My Practice Spreadsheets
folder on your hard disk and then close the workbook.

Working with the Date Functions

Excel contains a number of Date functions that help you work with dates in your
spreadsheets. These functions run the gamut from those that return the current date
to those that convert text to date serial numbers (so you can use them in formulas
that compute the number of days that have elapsed).

When you install and activate the Analysis ToolPak add-in program that comes with
Excel, you have access to even more Date functions, many of which are specifically
targeted to performing date computations involving the normal five-day, Monday
through Friday, workweek.

Try It

Exercise 7-2: Building Formulas with Common Date Functions
Open the Exercise7-2.xls workbook file in your Chapter 7 folder in the My Practice
Spreadsheets folder on your hard disk or on the Excel Workbook folder on the work-
book CD-ROM. This workbook contains several worksheets, the first two of which
give you practice using the Date functions built into Excel and are available to you
when you activate the Analysis ToolPak:

1. Insert the current date in cell B1 using Excel’s TODAY function.
When working with a new function, use the Insert Function button on the
Formula bar to enter the function. That way, you can learn the purpose of the
function in the Insert Function dialog box and what arguments, if any, the func-
tion requires through the use of the Function Arguments dialog box.

2. Insert the current time in cell D1 using the NOW function and then format it with
the Custom h:mm AM/PM time number format.