Page 125 - Excel Workbook for Dummies

12_798452 ch07.qxp 3/13/06 7:51 PM Page 108
108 Part II: Using Formulas and Functions
When entering times in a spreadsheet that you intend to use in formulas to compute
elapsed times, be sure to enter all times after 12 noon either using a 24-hour clock (as
in 13:05 for 1:05 PM) or with the PM signifier after the time number. Otherwise, Excel
interprets all the times you enter as occurring in the AM.

Try It

Exercise 7-1: Building Formulas that Calculate Elapsed Dates and Times
Launch Excel, if the program is not currently running, and use a blank workbook to
build some simple formulas that calculate elapsed times and dates:

1. Increase the display magnification percentage from 100% to 200% and then enter
the following headings in the designated cells of Sheet1 in the new workbook:
• Start in cell A1

• End in cell B1
• Elapsed in cell C1
2. Enter the following dates in the designated cells:
• 11-6-05 in cell A2
• 2-15-06 in cell B2

3. Construct a formula in cell C2 that subtracts the ending date from the starting
date.

Excel calculates the difference between the two dates and returns the result for-
matted as the date 4/10/1900 to cell C2.
4. Format cell C2 with the Number format and zero decimal places.
Excel now displays 101 as the answer in cell C2.
5. Enter the following dates in the designated cells:
• 4-26-04 in cell A3
• 7-21-06 in cell B3
• 11-30-03 in cell A4
• 5-19-06 in cell B4
6. Use the Fill handle to copy the formula in cell C2 down to the range C3:C4.
Because you formatted the calculated result in cell C2 before you copied its for-
mula, the results returned to cells C3 and C4 automatically appear formatted as
whole numbers rather than as dates.
7. Enter the following times in the designated cells:
• 8:12 AM in cell A6

• 2:15 PM in cell B6
8. Construct a formula in cell C6 that calculates the difference between the ending
time and the starting time.
Excel calculates the difference between the two times and returns the result for-
matted as the date and time 1/0/1900 6:03, or simply 6:03 or 6:03 AM (depending
upon your version of Excel), to cell C6.
9. Format cell C6 with the Number format with two decimal places.
Excel now displays 0.25 as the answer in cell C6.