How to Calculate Time Duration in Excel 2007

As long as Excel recognizes a number as a time of day or a date, it can perform calculations using the time. Therefore, for spreadsheets tracking time -- such as timesheets -- entering a start time and an end time is sufficient to calculate time duration; it's a simple matter of subtracting one from the other.

...
Excel can function like a clock, figuring the duration between a start time and an end time.

Step

Format the relevant cells as time by selecting them and choosing “Time” from the Number group drop-down menu in the Home tab.

Step

Enter a start time and an end time in different cells, such as the start time in cell A1 and the end time in cell B1. Ensure Excel hasn’t misinterpreted either time, such as making "9:15 pm" read "9:15 am." If necessary, type “am” or “pm” after the times or use a 24-hour clock, typing "21:15" instead of "9:15 pm." Investigate Excel’s Help menu on “time of day” if necessary to learn more about these formats.

Step

Subtract the start time from the end time to calculate the time duration. In this example, type “=B1-A1” and press “Enter.”

Step

Change the number format for the result, which automatically formats itself as a time of day, which is incorrect. For example, the duration between 2:00 pm and 2:30 pm reads “12:30 am,” which is 30 minutes after zero-hour, or midnight. Instead, choose “Custom” from the Number group drop-down in the Home tab to set the format correctly.

Step

Choose “Time” in the left-hand panel of the resulting dialog box. Click “13:30,” the second option in the right-hand list, to display the duration in hours:minutes -- in this case, "0:30." Click “37:30:55” for hours:minutes:seconds.