Browse » Home
Sunday, August 19, 2012
How to Calculate Time in Excel 2007
1. Open the Excel 2007 document in which you want to include time calculations.
2. Enter headings for your calculation columns. For example, in a new Excel 2007 spreadsheet, you might type “Start” in cell A1, “End” in cell B1, and “Total” in cell C1.
3. Enter start and end times in the cells under the first two headings you added. For example, type 11:30 AM in cell A2 and 12:15 PM in cell B2. Always enter AM and PM after the time, unless you are using 24-hour clock time.
4. Insert formulas in the third column to calculate difference in times: If you have start and end times in cells A2 and B2, respectively, in cell C2 you would type “=B2-A2” (without the quotes) to subtract the later time in cell B2 from the earlier time in cell A2.
5. Change the cell formatting of the columns involving time calculations. Unless you do this, you may see incorrect or no results in the cells of the time totals column, though a formula is still visible in the Formula bar when you click a cell in that column. To do a time-sheet-style display of time, you will need two different time formats. Select the cells containing the start and end times and click the box at the lower-right corner of the “Number” group on the “Home” tab. The “Format Cells” dialog box appears. On the “Numbers” tab, select “Time” from the “Category” list, and select “1:30 PM” from the “Type” list. Click OK to return to your worksheet.
6. Select the cells containing the calculated time totals and again click the box in the lower-right corner of the “Number” group. Select the “Time” category, but now click the second item in the “Type” list, “13:30.” Click OK. The cells to the right of the start and end times should now show the difference in times displayed in hours and minutes. For example, for a time of 11:30 AM in cell A2 and 12:15 PM in cell B2, cell C2 will display '0:45.'