How to Calculate Time in Excel 2007
Businesses often need to calculate times for invoices and employee time sheets in Excel spreadsheets. To calculate time in Excel 2007 and have it appear correctly, you need to write simple formulas and change Excel 2007’s cell-formatting options. The steps are simple, but precision is key. Improper cell formatting will return false or unusable time calculations.
Open the Excel 2007 document in which you want to include time calculations.
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.
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.
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.
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.
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."
Tips & Warnings
- Whether you are creating a time-calculation sheet from scratch or working in a preexisting worksheet, it’s easy to miss a cell, column or row of formatting and end up with incomprehensible results. To copy cell formatting down a column or across a row, select the cell, click the small black box at the lower corner of the cell, and drag it where you want the formatting to be copied. From the icon that appears in the lower right of the cells after you have released the mouse button, select “Fill Formatting Only.”