How to Calculate Hours Between Two Times in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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:

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

Calculate Hours Simply by Subtracting Two times in Excel


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.

Use HOUR Function to Compute Hours Between Two Times in Excel


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.

Calculate Hours Between Two Different Dates in Excel


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


<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
  1. Reply
    michael gamal aziz Mar 15, 2023 at 1:29 PM

    thankssssssssssss

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo