Exceljet

Quick, clean, and to the point

Time difference in hours as decimal value

Excel formula: Time difference in hours as decimal value
Generic formula 
=MOD(end-start,1)*24
Summary 

To get the duration between two times in as decimal hour (i.e. 3 hrs, 4.5 hrs, 8 hrs, etc.) you can use a formula based on the MOD function. In the example shown, the formula in D5 is:

=MOD(B2-A2,1)*24
Explanation 

Excel dates are serial numbers,  and a day is equivalent to the number 1.  This means 1 hour = 1/24 = 0.041666667. In other words, Excel times are just fractional numbers:

Time Fraction Hours
3:00 AM 0.125 3
6:00 AM 0.25 6
9:00 AM 0.375 9
12:00 PM 0.5 12
3:00 PM 0.625 15
6:00 PM 0.75 18
9:00 PM 0.875 21
12:00 AM 1 24

To convert these fractional values to decimal hours, just multiply by 24. For example .5 * 24 = 12 hours, .24 * 24 = 6 hours, etc.

Hours between times

To calculate hours between times, you can simply subtract the start time from the end time when both times are in the same day. For example, with start time of 9:00 AM and an end time of 3:00 PM, you can simply use this formula:

=(3:00 PM-9:00 AM)*24
=(.625-.375)*24
=.25 * 24
=6

Times that cross midnight

When times cross midnight, the problem becomes more tricky, since the end time will often be less than the start time.  One elegant way to handle this challenge is to add the MOD function to the formula. For example, to calculate hours between 9 PM and 3 AM:

=MOD(0.125-0.875,1)*24
=MOD(-0.75,1)*24
=0.25*24
=6

The MOD function takes care of the negative problem by "flipping" negative values to the required positive value. (In this way, the MOD function works a bit like a clock. Here's a good link on Khan Academy that explains more about modulo.)

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.