To correct this problem, you can use this formula for times that cross a day boundary: =1-start+endīy 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. 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 (#). This means times and hours are fractional values of 1, as shown in the table below: HoursĬalculating elapsed time is more tricky if the times cross a day boundary (midnight). In Excel, one day equals 1, which represents 24 hours. However, when times cross a day boundary (midnight), things can get tricky. To see the result in hours and minutes, apply a time number format like this: h:mm For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula: =end-start When start time and end time occur in the same day, calculating duration in hours is straightforward. This article provides several formula solutions, depending on the situation.
This is because times that cross midnight often have a start time that is later than the end time (i.e. The complexity of calculating the number of hours between two times stems from times that cross midnight.
