Often you may want to calculate the number of hours between two times after midnight in Excel.
You can use the following formula to do so:
=(B2-A2+TRUE)*24
This particular example calculates the number of hours between the starting time in cell A2 and the ending time in cell B2, assuming midnight occurs between these two times.
The following example shows how to use this formula in practice.
Example: Calculate Hours Between Two Times After Midnight in Excel
Suppose we have the following lists of start and end times for some task in Excel:
Notice that midnight occurs between each start and end time.
We can type the following formula into cell C2 to calculate the difference in hours between the first start and end time:
=(B2-A2+TRUE)*24
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows the difference in hours between the start time and end time of each row.
For example:
- The difference between 4:15 PM and 4:13 AM is 11.97 hours.
- The difference between 5:19 PM and 5:00 AM is 11.68 hours.
- The difference between 6:00 PM and 5:15 AM is 11.25 hours.
And so on.
Note: If your formula returns a time rather than a decimal number of hours, you must highlight the cell range C2:C10 and then click the Number Format dropdown menu from the Home tab and then click General:
This will convert all values in a time format into a decimal number format.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Time Duration to Minutes
Excel: Calculate Difference Between Two Times in Minutes
Excel: Calculate Difference Between Two Times in Hours
