
How to calculate the difference between two times in Excel
Problem
If you have an Excel sheet with two columns “Check In” and “Check Out” in Time Format also and want to calculate the working hours between the two values which can be calculated with the following formula:
Working Hours = Check Out Time – Check in Time
1- What you want to do
2- Result
You can find the result is different than you want, the expected correct answer is
(8 Hours), which is different than what appeared in the result photo
Our Solutions:
Excel has a number of formulas that enable you to count the time between two dates. You can specify that you would like to count the difference in hours by adding a time specified to your formula or by altering the cell format in your spreadsheet. Excel also enables you to apply your formula to multiple cells simultaneously by clicking the corner of your primary formula cell and dragging it with your mouse. As long as you have a start time and an end time, Excel will be able to count the hours between them.
Solution 1:
You can use Text function to change the format , also you can specify the result format which you want to get “h:mm” – “h” stands for “Hours” – “mm” stands for “Minutes”
You can also use one of the following formula
Formula | Description |
Text Function | |
=TEXT(E3-D3,”h”) | Hours between two times with the cell formatted as “h” |
=TEXT(E3-D3,”h:mm”) | Hours and minutes between two times with the cell formatted as “h:mm” |
=TEXT(E3-D3,”h:mm:ss”) | Hours, minutes, and seconds between two times with the cell formatted as “h:mm:ss” |
Solution 2:
You can use INT function to change the format, also you can specify the result format which you want to get as (Hours
– Minutes – Seconds).
Note: in this solution you will neglect the frictional part of hour
Formula | Description |
INT Function | |
=INT((E3-D3)*24) | Total hours between two times |
=INT((E3-D3)*1440) | Total minutes between two times |
=INT((E3-D3)*86400) | Total seconds between two times (17700) |
Solution 3:
You can use on of the following functions directly
Formula | Description |
=HOUR(E3-D3) | The difference in the hours unit between two times. |
=MINUTE(E3-D3) | The difference in the minutes unit between two times |
=SECOND(E3-D3) | The difference in the seconds unit between two times. |