How to Calculate Time Duration in Excel 2007

by Sonia Waring

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.

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

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.

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

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.

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.

About the Author

Sonia Waring has been a professional technology editor since 1998, focusing primarily on software reviews and how-to articles. She also edits copy for scientific journals, several websites, and an independent network security consultancy.

Photo Credits

  • photo_camera Ryan McVay/Photodisc/Getty Images