How to Calculate the Number of Hours Between 2 Times in Excel
Whether you are creating a time sheet for employees or tracking your workouts, calculating the difference between two times can be a useful measure for further calculations or averages. Although Excel comes with many powerful functions, including date and time functions, calculating time that elapses can be somewhat less than intuitive. If you anticipate calculating time differences of less than 24 hours, you can use the straightforward HOUR function; otherwise, you will need to build a different formula that takes lengthier time differences into account.
When Less than 24 Hours
Enter a time in the first cell in the "hh:mm:ss" format and add "AM" or "PM" if necessary. Enter a second time in another cell in the same format. If you do not enter "AM" or "PM," Excel will default to "AM." For example:3:48 PM
Select both cells and click the formatting window in the Number group on the "Home" tab. The formatting window may display "General" or something else. Select "Time" to format the cells as time.
Click an empty cell. Enter an equal sign followed by the word "hour" without quotes. Enter an open parenthesis. Click the cell with the later time you entered, followed by a "-" sign. Click the first cell with the earlier time you entered. Enter a closing parenthesis. Press "Enter" to subtract the first time from the second time. For example:=HOUR(E8-D8)
When 24 Hours or More
Enter a date and time in one cell and a date and time in a second cell, using the "mm/dd/yyy hh:mm:ss" format. Add "AM" or "PM", if necessary. Include spaces between the date, time and AM or PM. For example: 5/21/2011 3:48 PM
Select both cells and click the formatting window in the Number group on the "Home" tab. The formatting window may display "General" or something else. Select "More Number Formats" to display the Format Cells window. Select "Time" from the Category list on the left side and scroll through the options in the Type window to select one of the date and time formats, such as "3/14/01 1:30 PM." Click "OK."
Click an empty cell. Enter an "=" sign, followed by the word "INT" without quotes. Enter two open parentheses. Click the cell with the later time you entered followed by a "-" sign. Click the first cell with the earlier time you entered. Enter a closing parenthesis. Enter "*24" without quotes and add the final parenthesis. Press "Enter." For example:=INT((E8-D8)*24)
Tips & Warnings
- Use the second method if you are unsure whether time differences might be more or less than 24 hours.
- Note that Excel will round hours down. For example, Excel will round a time difference of three hours and 55 minutes down to three hours.