In this article we will discuss why calculating durations can be tricky and sometimes gives the “wrong results”.
Check out Durations example spreadsheet (XLS 14 KB) for the example referred to in this article. There are further examples of duration calculations and conversions of durations to hours or minutes in the Dates and Times example spreadsheet (XLS 56 KB).
If you want to work out the duration of an event, then provided you know the days and times when it started and finished then it should be just a simple matter of subtraction. However it is often just not that simple. It depends on how the starting and finishing days and times are stored and also how the result is formatted.
We’ve set up a spreadsheet showing some of the likely situations and the desired outcomes. The first two scenarios are thesame except for different time formatting. The third set (row 4) shows a starting time on one day and a finishing time early in the morning of the next day. The last two rows show both the dates as well as times. The last row also introduces the complexity of having a duration of > 24 hours.
Working just with times
If we focus on just times we have to assume we are dealing with durations of <= 24 hours. Ignoring the complication of the time period going through midnight we can simply subtract the two times as in the adjacent formula. The only problem we now get is that the result is automatically formatted as a time rather than as a duration. This is easily fixed by reformatting using a 24 hour format as in the first diagram. Here the format used was of the form hh:mm (more about this later).
Now for the next complication. The simple subtraction formula, appropriately formatted, will work for any duration of less than 24 hours that doesn’t go through midnight. How do you get around the problem of elapsed time periods that apparently finish before they begin? The easiest way is to test for this first and add on 1 (i.e. a whole day) in the formula to reflect that the finish time in this case is on the next day. Don’t, as many students want to do, add on 24, as this is really saying the finish time is 24 days later! The revised formula shown here for cell C4 will of course work for C2 and C3 as well. (There is a more geeky solution that can be found on the Internet but we won’t use that here!)
Working with values containing dates as well as times
If the start and finish dates are included as well as the times, then provided the data is sensible, a simple subtraction formula should give the correct answer (although there might again be formatting problems). This is because the numbers being subtracted now include the date serial numbers, so an event occurring on the next day, will be a have a larger number. In row 6 below, the numbers are 39,029.9306 for 8/11/2006 10:20 p.m. and 39,032.1250 for 11/11/2006 3:00 a.m. (Check out the Dates and Times article if you don’t understand where these numbers come from.) So why is the value displayed in cell C5 ok, whereas the values shown for C6 is clearly wrong?
The calculation of C6 is correct (answer is actually 2.1944 as formatting C6 as a number will show) and once again it is a formatting problem. What we need is an elapsed time format. Excel makes this hard.In earlier versions it had a time format of type 37:30:55 as well as the normal 13:30:55 type. 
The 37:30:55 type was appropriate for formatting durations of greater than 24 hours. Nowadays a custom format is required. If cell C6 is reformatted using custom format [hh]mm then the correct answer of 52:40 is displayed.
Converting Durations to Hours and Minutes
The durations that we have been calculating are sort of date/time numbers where the integer part represents whole days rather than a date. Thus a duration of 2 days and 4 hours and 40 minutes can be formatted to display as 52:40 hours but the underlying number is 2.1944, i.e., 2 for the whole days and .1944 for the extra 4 hours and 40 minutes. Similarly a duration of 5:10 hours is 0.2153 of a day, as reformatting cells C2 or Cell C3 as numbers, will show. A duration of 17:10 hours (12:00+5:10) will be 0.7153.
Sometimes it is necessary or desirable to convert durations into whole hours or minutes. For example, rates for telephone calls are often quoted in terms of rounded up minutes. Our Durations example spreadsheet (XLS 14 KB) has columns showing the durations converted into hours and rounded-up minutes.
What is the best way of converting a duration to hours or minutes?
The most reliable method for converting a duration into hours is to use simple logic rather than use built-in time functions that were designed for a different purpose.
There are 24 hours in a day and 24*60 minutes in a day, so the easiest way to convert a duration stored as a date/time number into hours, is simply to multiply the duration by 24. This works in every situation. On the other hand the formula shown on the right above doesn’t work for the case illustrated, although it does work for the rest of the column. This is because the HOUR, MINUTE and SECOND functions are designed to work with times of the day. The 2 whole days part of the C6 duration is ignored by the HOUR function and not converted to 48 hours to be added onto the other 4 hours. The formula can be cured (see Dates and Times example spreadsheet (XLS 56 KB)) but the simpler formula, used on the left, is the better solution.
Using the same sort of logic it is easiest to enter the formula =ROUNDUP(C2*24*60,0) into E2 and copy it down the column to accurately find the rounded-up number of minutes in each duration. On the other hand in a situation where charging or points awarded etc are for completed minutes, then a similar formula using ROUNDOWN would be appropriate.