Calculate Hours Between Two Times in Excel (6 Methods)

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.


Download the Practice Workbook

Download the Excel file and practice along with it.


6 Methods to Calculate Hours Between Two Times 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 total hours. Now, let’s have a sneak peek of our dataset:

Basic Dataset

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Calculate Hours Simply by Subtracting Two times in Excel

The most basic way of calculating time in hours between two times is to subtract those two times. But we need to ensure one thing that is 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.

Calculate Hours Simply by Subtracting Two times in Excel

Read More: How to Subtract and Display Negative Time in Excel (3 Methods)


2. Use 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 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.

Use HOUR Function to Compute Hours Between Two Times in Excel

Read More: Excel formula to calculate hours worked & overtime [with template]


3. Use TEXT Function to Calculate Hours Between Two Times in Excel

You can use the TEXT function instead of using the HOUR function to directly calculate the hours between two times.

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:

Read More: How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)


Similar Reading


4. Calculate 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.

Calculate Hours Between Two Different Dates in Excel

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


5. Use 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.

As 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: Excel Calculate Hours between Two Times after Midnight (3 Methods)


6. Count 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.

In the standard time format, it 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 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: How to Calculate Hours and Minutes in Excel (7 Handy Ways)


Things to Remember

📌 If a cell doesn’t have enough space to show the entire time value, then Excel returns #### error.

📌 Adjust the cell width to fix the #### issue.


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 along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo