Method 1 – Calculating Hours Between Two Times 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.
Steps:
- Insert the following formula in cell D5:
=C5-B5- Press the Enter key.
- Drag the Fill Handle icon to the end of the Total Hours column.
Method 2 – Using the HOUR Function to Compute Hours Between Two Times in Excel
Steps:
- Select cell D5 to insert the following formula:
=HOUR(C5-B5)- Hit Enter.
- Drag the Fill Handle icon to the end of the Total Hours column.
Method 3 – Using the TEXT Function to Calculate Hours Between Two Times in Excel
Steps:
- Input the following formula in cell D5:
=TEXT(C5-B5, "h")- Press Enter.
- Drag the Fill Handle icon to the end of the Total Hours column.
Method 4 – Calculating Hours Between Two Different Dates in Excel
Steps:
- Insert the formula below within cell D5.
=INT((C5-B5)*24)- Hit Enter and drag the Fill Handle icon to the end of the third column of the data table.
Note: The number format of the result column has to be General.
Method 5 – Using the IF Function to Calculate Hours Between Two Times in Excel
This method eliminates negative values by always subtracting the earlier time from the later one.
Steps:
- Insert the formula below in cell D5.
=IF(C5>B5,C5-B5,1-B5+C5)- Press Enter and drag the Fill Handle icon to the end of the Total Hours column.
Method 6 – Counting Elapsed Time in Hours from a Starting Time to Now
Steps:
- Enter the following formula in cell D5.
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) -B5- Hit Enter.
- 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, Excel returns ####. Adjust the cell width to fix the issue.
Download the Practice Workbook
Download the Excel file and practice along with it.
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
Thee formula is not worning
Hello Fikile,
Thanks for your comment and for trying the steps! Since you didn’t mention which formula you used, could you please share:
The exact formula you entered. A small example of your start time and end time (e.g., Start: 8:30 AM, End: 5:00 PM). Your Excel version (e.g., Excel 2016, Microsoft 365).
In the meantime, please also check these common issues:
Make sure both start time and end time are real time values, not text.
If you’re subtracting times, try a simple example like:
=B2-A2
and format the result cell as [h]:mm.
If you’re using a formula from the article, ensure the cell references match your sheet exactly.
Share your formula and sample data, and I’ll gladly help you fix it step by step.
Regards,
ExcelDemy