If you have two times in two different cells in Excel and want to calculate the difference in hours, then you are in the right place. We will show you 6 different methods that you can use to calculate hours between two times in Excel.
1. Calculating Hours Between Two Times Simply by Subtracting Them in Excel
We’ve created the following data table to calculate the hours between two times in Excel. The table consists of 3 columns. The first column contains the starting time, the second column contains the ending time and the third column contains the total hours. Now, let’s have a sneak peek of our dataset:
The most basic way of calculating time duration between two times is to subtract those two times. But we need to ensure one thing: we have to subtract the starting time from the end time. Otherwise, the result will be negative.
To do that, follow the steps below.
🔗 Steps:
❶ Type the following subtraction formula within cell D5.
=C5-B5❷ After that press the ENTER button.
❸ Lastly, finish the whole process by dragging the Fill Handle icon to the end of the Total Hours column.
2. Using the HOUR Function to Compute Hours Between Two Times in Excel
In the following data table, we have the starting time in the first column and the ending time in the second column. Now we will calculate the differences between the starting time and the ending time of a session using the HOUR function.
We will store the output of the HOUR function in the third column of the data table whose header is total hours.
Now follow the steps below.
🔗 Steps:
❶ You have to select cell D5 to insert the following formula:
=HOUR(C5-B5)❷ After inserting the formula, you have to press the ENTER button to get the result of the HOUR function.
❸ Lastly, drag the Fill Handle icon to the end of the Total Hours column.
3. Using the TEXT Function to Calculate Hours Between Two Times in Excel
You can use the TEXT function instead of using the HOUR function to calculate the hours between two times directly.
For that purpose, you can follow the steps below.
🔗 Steps:
❶ Type the following formula on cell D5.
=TEXT(C5-B5, "h")❷ Now hit the ENTER button to execute the formula.
❸ Finally, drag the Fill Handle icon to the end of the Total Hours column.
This formula can return the hours between two times directly as in the picture below:
4. Calculating Hours Between Two Different Dates in Excel
Suppose, you want to compute the difference between two times of two different dates in hours. Excel will allow you to do so simply by subtracting two cells and using the INT function to trim the trailing numbers after the decimal point.
Now, follow the steps below.
🔗 Steps:
❶ Insert the formula below within cell D5.
=INT((C5-B5)*24)❷ Now hit the ENTER button and pull the Fill Handle icon to the end of the third column of the data table.
💡 Note: The number format of the column where you typed the formula, has to be General.
5. Using the IF Function to Calculate Hours Between Two Times in Excel
We can count the difference between two times in hours using logic with the IF function.
To calculate time with a positive value, we need to subtract the start time from the end time, we will first compare the two times to meet this criterion. Anyways, follow the steps below:
🔗 Steps:
❶ Insert the formula below on cell D5.
=IF(C5>B5,C5-B5,1-B5+C5)❷ Then press the ENTER button and drag the Fill Handle icon to the end of the Total Hours column.
Read More: Calculate Elapsed Time Between Two Dates in Excel
6. Counting Elapsed Time in Hours from a Starting Time to Now
We can count the total elapsed time in hours from a certain starting time period. In this regard, we can get the current time easily with the help of the NOW function.
The standard time format consists of three parts which are hour, minute, and second. To retrieve these, we will use the HOUR, MINUTE, and SECOND functions respectively.
On top of that, we have to use the TIME function to constitute the standard time format with hours, minutes, and seconds.
To do that, follow the steps below.
🔗 Steps:
❶ Enter the following formula within cell D5.
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) -B5❷ After that press the ENTER button.
❸ Finally drag the Fill Handle icon to the end of the Total Hours column.
Formula Breakdown
- HOUR(NOW() ▶ returns the current hour of time.
- MINUTE(NOW() ▶ returns the present minute.
- SECOND(NOW() ▶ returns the current second of time.
- TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) ▶ constitutes standard time formula of the current time.
Read More: Excel Calculate Hours Between Two Times After Midnight
Things to Remember
📌 If a cell doesn’t have enough space to show the entire time value, then Excel returns the #### error.
📌 Adjust the cell width to fix the #### issue.
Download Practice Workbook
Download the Excel file and practice along with it.
Conclusion
To sum up, we have discussed 6 methods to calculate hours between two times in Excel. You are recommended to download the practice workbook attached to this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
Related Articles
- How to Calculate Hours and Minutes in Excel
- How to Calculate Hours from Date and Time in Excel
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Difference Between Two Dates and Times in Excel
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- How to Calculate Travel Time Between Two Cities in Excel
<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
thankssssssssssss
Dear Michael Gamal Aziz,
You are most welcome.
Regards
Shamima Sultana
Project Manager | ExcelDemy