Building Formulas that Calculate Dates and Times

In this tutorial building simple formulas that calculate the differences between different dates and different times entered into the cells of a worksheet.

1. If Excel is not running, launch the program and use its Book1 to build some simple formulas that calculate elapsed times and dates. If the program is already running, click the New button on your customized Quick Access toolbar or press Ctrl+N to create a new workbook for this purpose.
   You will start by entering some column headings in row 2 of the new worksheet.
2. Use the Zoom slider to increase the display magnification percentage of the Sheet1 worksheet from 100% to 200% and then enter the following headings in the designated cells:
   
   • Start in cell A2
   • End in cell B2
   • Elapsed in cell C2
3. Enter the following dates in the designated cells:
   
   • 11-6-07 in cell A3
   • 2-15-08 in cell B3
4. Construct a formula in cell C3 that subtracts the starting date from the ending date, that is, =B3-A3.
   Excel now displays 101 as the answer in cell C3.
5. Enter the following dates in the designated cells:
   
   • 4-26-04 in cell A4
   • 7-21-06 in cell B4
   • 11-30-03 in cell A5
   • 5-19-06 in cell B5
6. Use the Fill handle to copy the formula in cell C3 down to the range C4:C5.
   Excel finds the difference between the dates in cells B4 and A4 to be 816 days and the difference between the dates in cells B5 and A5 to be 901 days.
7. Enter the following times in the designated cells:
   
   • 8:12 AM in cell A7
   • 2:15 PM in cell B7
8. Construct a formula in cell C7 that calculates the difference between the ending time and the starting time, that is, =B7-A7.
   Excel calculates the difference between the two times and returns 6:03 AM to cell C7. You need to convert this time into a simple number by formatting it with the Number format.
9. Click the drop-down button attached to Number Format on the Ribbon's Home tab and then click the Number option on its drop-down menu to format cell C7 with the Number format with two decimal places.
   Excel now displays 0.25 as the answer in cell C7.
10. Convert the fraction of the day into hours by editing the formula in cell C7 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 parentheses so that Excel performs the subtraction first and then multiplies this difference by 24, as in
    =(B7-A7)*24
    After you edit the formula, Excel returns 6.05 as the number of hours in cell C7.
11. Enter the following times in the designated cells:
    
    • 7:35 AM in cell A8
    • 15:05 in cell B8
    • 9:45 in cell A9
    • 6 PM in cell B9
12. Use the Fill handle to copy the formula in cell C7 down to the range C8:C9.
    Because you edited the basic formula in cell C7 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 C8 and C9 automatically return the number of hours expressed with two decimal places, 7.50 and 8.25, respectively.
13. Position the cell cursor in cell A1 and then save this new workbook with the filename Date and time frmls.xlsx in the Excel 2007 Practice folder and then close the workbook.