< Go Back

Calculate number of hours between two times

Do you want to learn how to calculate the number of hours between two times? This post will give you the knowledge needed to calculate the number of hours between two times, something useful if you want to manage your free time using Excel.

Figure 1. Calculate the number of hours between two times example datasheet

Formula

=end-start

How does this formula to calculate the number of hours between two times work?

If you want to calculate the number of hours between two times, the easiest way would be to type a formula that compares two different times and then subtracts one from another.

How time works in Excel

The way time works in Excel is by assigning the number 1 to one day, and one day represents an amount of time of 24 hours, and Excel reads times and hours as a fraction of the number 1. See the image below for a better understanding.

Figure 2. Calculate the number of hours between two times using fractions of 1

Calculate the number of hours between two times in one single day

If you want to calculate two different times within one single day you can use a simple subtraction formula. For example, if you want to calculate how many hours are between 7:00 AM and 3:00 PM you can use the following formula:

=end-start
=3:00PM-7:00AM
=0.63-0.291666
=.33 = 8hours

Calculating hours past midnight

Since Excel reads a day as 24 hours, the method used to calculate the number of hours between two times needs to be tweaked when one of the times passes the 24-hour mark. For example, if you want to calculate the time between 11:00 P.M (0.958333) and 3:00 A.M (0.125), the finishing time will be a lower number than the starting time, and if you use the previous formula the result will be a negative value that will be shown as an error. To fix this issue you can do use the following formula instead:

=1-start+end

Calculate the number of hours between two times using a single formula

=IF(end>start,end-start,1-start+end)

This formula will use the formula used the times use are within one single day, and it will use the other one if the end time has a larger value than the start time.

Adding a MOD Function to a formula to calculate the number of hours between two times

=MOD(end-start,1)

If you use the MOD function in this formula everything will be simpler, as it will turn any negative values to positives if it is required, this means that it will be able to handle both scenarios without complications.

Using different time formats

Excel displays time using AM or PM by default, so if you used a formula that gave you “4:00 AM” as a result, you may want to change it to hide the “AM”, you can do this by applying a personalized time format like h:mm.

When the calculated hours are over 24, you should use a custom time format, the [h]:mm is a good one, as it will make Excel show hour durations larger than 24 hours, if you use some of the standard formats, Excel may just “roll over” if the duration exceeds 24 hours just like a regular clock.

Using date and time to calculate the number of hours between two times.

Everything can be made even easier if you input both date and time to make your calculations. If you want to input a date and a time on the same cell, just type them normally and then divide them with your spacebar: 17/10/2018 8:35 PM.

After that, just use the following formula to calculate the number of hours between two times.

=end-start

Figure 3. Calculate the number of hours between two times using both date and time, showing results using a custom [h]:mm:ss format

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar