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:
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.)
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
