Calculate number of hours between two times

Calculate number of hours between two times

Generic formula 

=end-start

Related formulas 

Time difference in hours as decimal value

Basic overtime calculation formula

Convert time to time zone

Explanation

To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (midnight), things can get tricky. Read below to see several ways to manage this challenge.

How Excel tracks time

In Excel, one day (24 hours) is represented by the number 1. So 1 hour is 0.041666667 (i.e. 1/24), 8 hours is 0.333, 12 hours is 0.50 and so on. In short, you can think of hours as fractional pieces of a day.

When the start time and end times are both in the same day, then the start time is, by definition, less than the end time and you can use simple subtraction to figure out elapsed time. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:

end - start = elapsed time0.375 - 0.708 = .333 // 8 hours

The problem

Calculating elapsed time is more tricky if the times cross a day boundary. For example, if the start time is 10:00 PM one day, and the end time is 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value which will cause Excel to display a string of hash characters (i.e. ########).

To correct this problem, you can use this formula for times that cross a day boundary:

=1-start+end

By subtracting the start time from 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as the end time.

This formula won't work for times in the same day, so we can generalize and combine both formulas inside an IF statement like so:

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

Now when both times are in the same day, end is greater than start time, so the simple formula is used. But when the times across a day boundary the second formula is used.

This can be further simplified to this elegant formula:

=MOD(end-start,1)

Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value.

A thorough discussion of modulo is beyond the scope of this article, but here's a good link on Khan Academy.

So, the formulas above will handle either case (both times in the same day, or start in one day and end in the next). However, note that they only work for times that span just one day. If times span more than one day, you'll need a different approach. One approach is to use both date and time, as explained below.

Formatting time durations

By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM. To remove the AM/PM, apply a a custom number format like h:mm. 

In cases where calculated time exceeds 24 hours, you may want to use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

Simplifying the problem

You can simply the problem of calculating elapsed time by working with values that contain both date and time. To enter a date and time together, use a single space between time and date: 9/1/2016 10:00 AM

Then you can use a basic formula to calculate elapsed time:

=end-start

In the example below start and end values contain both dates and times:

The formula is:

=C5-B5

Formatted with the custom number format [h]:mm, to display elapsed hours.

Calculate number of hours between two times

Generic formula 

=end-start

Related formulas 

Time difference in hours as decimal value

Basic overtime calculation formula

Convert time to time zone

Explanation

To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (midnight), things can get tricky. Read below to see several ways to manage this challenge.

How Excel tracks time

In Excel, one day (24 hours) is represented by the number 1. So 1 hour is 0.041666667 (i.e. 1/24), 8 hours is 0.333, 12 hours is 0.50 and so on. In short, you can think of hours as fractional pieces of a day.

When the start time and end times are both in the same day, then the start time is, by definition, less than the end time and you can use simple subtraction to figure out elapsed time. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:

end - start = elapsed time0.375 - 0.708 = .333 // 8 hours

The problem

Calculating elapsed time is more tricky if the times cross a day boundary. For example, if the start time is 10:00 PM one day, and the end time is 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value which will cause Excel to display a string of hash characters (i.e. ########).

To correct this problem, you can use this formula for times that cross a day boundary:

=1-start+end

By subtracting the start time from 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as the end time.

This formula won't work for times in the same day, so we can generalize and combine both formulas inside an IF statement like so:

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

Now when both times are in the same day, end is greater than start time, so the simple formula is used. But when the times across a day boundary the second formula is used.

This can be further simplified to this elegant formula:

=MOD(end-start,1)

Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value.

A thorough discussion of modulo is beyond the scope of this article, but here's a good link on Khan Academy.

So, the formulas above will handle either case (both times in the same day, or start in one day and end in the next). However, note that they only work for times that span just one day. If times span more than one day, you'll need a different approach. One approach is to use both date and time, as explained below.

Formatting time durations

By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM. To remove the AM/PM, apply a a custom number format like h:mm. 

In cases where calculated time exceeds 24 hours, you may want to use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

Simplifying the problem

You can simply the problem of calculating elapsed time by working with values that contain both date and time. To enter a date and time together, use a single space between time and date: 9/1/2016 10:00 AM

Then you can use a basic formula to calculate elapsed time:

=end-start

In the example below start and end values contain both dates and times:

The formula is:

=C5-B5

Formatted with the custom number format [h]:mm, to display elapsed hours.