How many hours between two times?
To get hours between two times, use the subtraction formula.
A result is a decimal number (You might get a different result if you have a different format cell). The numbers are in a day unit, to get the numbers in hour multiply the results by 24.
The formula
=(B2-A2)*24
The result
If the result is less than 1, then the difference between two times is less than 1 hour.
How many minutes between two times?
It’s like looking for hours between two times. To get minutes between the two times, use the subtraction formula, but multiply the result by 1440. Where did the number 1440 come from? The number comes from the multiplication of numbers 24 and 60.
The formula
=(B2-A2)*1440
The result
How many seconds between two times?
To get seconds between two times, use the subtraction formula and multiply the result by 86400.
The formula
=(B2-A2)*86400
The result
All results are integer number because seconds are the smallest units in time.
How many hours, minutes and seconds between two times?
The three formulas above only return numbers in one unit, hours only, minutes only or seconds only. How to get the complete difference in hours, minutes and seconds?
Solution 1: Subtraction formula and format cell
Subtraction formula is the easiest solution, requires only one subtraction formula. The result will be a decimal number like the first picture above. To get the difference in details, do a format cell with the “h:mm:ss” format.
The result
Solution 2: HOUR, MINUTE and SECOND Function
The result of solution 1, all the differences are in one column. If you want the difference in hours to be in its column as well as the difference in minutes and seconds, the solution is using HOUR, MINUTE and SECOND function.
The formula for hours column
=HOUR(B2-A2)
The formula for minutes column
=MINUTE(B2-A2)
The formula for seconds column
=SECOND(B2-A2)
The result
How many days, hours, minutes and seconds between two times?
What if the difference in time is more than one day, how to calculate it? Use the subtraction formula.
The formula
=B2-A2
The result
The number in front of the decimal separator is the difference in days, while the number behind the decimal separator is the difference in hours, minutes and seconds.
For more informative, you can separate the difference in days, hours, minutes and seconds in each column. You need the QUOTIENT and MOD function to separate integer numbers and decimal numbers.
The integer number is the difference in days, and the decimal number is the difference in hours, minutes and seconds. You need the HOUR, MINUTE and SECOND functions to extract the details.
The formula for days column
=QUOTIENT(B2-A2,1)
The formula for hours column
=HOUR(MOD(B2-A2,1))
The formula for minutes column
=MINUTE(MOD(B2-A2,1))
The formula for seconds column
=SECOND(MOD(B2-A2,1))
The result
The difference in days is more than 30 days, and some are more than 365 days. Read the following article to find out how to calculate the difference in years, months, weeks and days between two dates
