In Excel, one day is the number 1, so 1 hour = 1/24 = 0.041666667. In other words, hours are just fractional parts of a day:
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:
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.)
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 (...
To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. In formula in cell I5 is: = ( F5 * H5 ) + ( G5 * H5 * 1.5 ) How this formula works Note: it's important to understand that Excel deals with time...
To convert hours in decimal format to a value Excel recognizes as time, divide by 24. In the example shown the formula in C5 is: = B5 / 24 which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which...
To convert minutes in decimal format to a proper Excel time, divide by 1440. In the example shown, the formula in C6 is: = B6 / 1440 Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since 60 minutes = 1 hour = 1/24...
To convert seconds in decimal format to a proper Excel time, divide by 86400. In the example shown, the formula in C6 is: = B6 / 86400 To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm. How this...
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.