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.

...
There are two main ways to get a time difference calculation.

When Less than 24 Hours

Step

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:

Step

3:48 PM

Step

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.

Step

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:

Step

\=HOUR(E8-D8)

When 24 Hours or More

Step

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:

Step

5/21/2011 3:48 PM

Step

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.”

Step

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:

Step

\=INT((E8-D8)*24)