Note: the complexity of the formulas on this page is a result of times that cross midnight. You can dramatically simplify things by using date values that contain times (“datetimes”), as explained below.
Simple duration calculation
When start time and end time occur in the same day, calculating duration in hours is straightforward. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula: To see the result in hours and minutes, apply a time number format like this: However, when times cross a day boundary (midnight), things can get tricky.
How Excel tracks time
In Excel, one day equals 1, which represents 24 hours. This means times and hours are fractional values of 1, as shown in the table below:
When times cross midnight
Calculating elapsed time is more tricky if the times cross a day boundary (midnight). 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, and Excel will display a string of hash characters (########). To correct this problem, you can use this formula for times that cross a day boundary: 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 that occur the same day, so we need to use IF function like this: When both times are in the same day, end is greater than start time, the simple formula is used. But when the times across a day boundary the second formula is used.
MOD function alternative
The MOD function provides an elegant way to simply the formula above. By using the MOD function with a divisor of 1, we can create a formula to handle both situations: This formula takes care of the negative time by using the MOD function to “flip” negative values to the required positive value. Because this formula will handle times in the same day and times that span midnight, we don’t need a conditional IF statement. Note: neither formula above will handle durations greater than 24 hours. If you need this, see the date + time option below. For more on modulo, here’s a good link on Khan Academy.
Simplifying with date + time
You can simply the problem of calculating elapsed time drastically using values that contain both date and time, sometimes called “datetimes”. To enter a date and time together, use a single space between time and date like this: 9/1/2016 10:00. If you format this date with General format, you’ll see a value like this: The numbers to the right of the decimal represent the time component of the date. Once you have datetime values, you can use a basic formula to calculate elapsed time. In the screen below, start and end values contain both dates and times, and the formula is simply:
The result is formatted with the custom number format: to display elapsed hours. This formula will correctly calculate the hours between two times in a single day, or over multiple days.
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 custom number format like: In cases where calculated time may exceed 24 hours, you should 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).
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.
