Let’s say that to make a point about the relationship between hot water usage and the size of the utility bill, you want to track how long your teenager spends in the shower. Or, in planning for a lawn care business, you want to track times required to mow lawns of various sizes. Excel would be a good tool for these kinds of tasks.
Excel’s ability to perform complex calculations is well known, but asking it to return simple time calculations can produce confusing results unless you understand all the factors involved.
At the heart of date/time values in Excel is the fact that they are stored as serial numbers that indicate days and decimals of days since the beginning of January, 1900. Excel refers to these as serial numbers, but they are not serial numbers as you may be used to seeing them as a mix of letters and numbers. They are instead numbers stored in a dddd.tttt format, where the digits to the left of the decimal point tell the number of days since January 1900 and the decimal tells the part of the day that has passed since midnight. This format is known as “serial date” or “serial date-time”. If you enter the current date and time in a cell and set the format to General or Number, you will see the date’s serial number version. April 13, 2009 1:39 pm is represented by the number 39916.56875. Understanding how Excel looks at dates and times can help us to demystify time calculations.
In this example I wanted to know the elapsed time between the first and second values:
As you can see I wanted to calculate how much time elapsed between 8:30 AM and 10:30 PM by subtracting the 8:30 start time value in cell A2 from the 10:30 PM end time in cell B2. The start and stop times were entered with AM and PM formats. Behind the scenes, Excel interpreted the start and stop times in terms of a 24 hour clock and saw the problem this way:
22:30 – 8:30 = 14:00
By default, Excel applied the AM/PM format to the result and returned 2:00 PM which is 14:00 on a 24 hour clock. So it calculated the correct answer – 14 hours of elapsed time – but I want to display that answer in hours and minutes.
To fix this problem, give cell C2, the result cell, the preferred format. Open the Format Cells dialog box and choose the Custom category on the Number tab. Then in the choices at the right, choose the h:mm format. The difference or elapsed time is 14 hours.
Note that the same problem with the start/end times entered with 24 hour time shows how Excel worked with the AM/PM time entries.
What does Excel do when the ending hour is actually a smaller number than the starting hour? This type of situation occurs when a worker begins a shift at 11:00 pm and ends at 7:00 am the next morning. What is important to remember in this time calculation is that while our focus might be on the number of hours and minutes that have elapsed, the number describing the day has also advanced by one as well.
In the example below you can see the result of the simple formula to subtract an evening start time from a next morning end time. The formula returns a negative number so Excel displays the #### symbols.
You can use the IF function to accurately calculate spans of time that stretch from one day to the next . The formula below contains the simple subtraction of the start value from the end value; but it also tests, using the IF function, the value of the end time to see if it is smaller than the start time. In cases where the end time is smaller, 1 (the value of another day) is added to the result.
Another surprising result can appear when adding several amounts of time that total more than 24 hours as in the example below:
You can see that the intent is to show total hours worked in cell B6 using the Sum function. It is easy to see by quick estimation that the total of hours worked was more than 9 hours and 45 minutes. Once again this is a cell formatting problem.
Excel seems to assume that you know these amounts add up to a day or days and some amount of hours and minutes more than a day. Given the current format applied to cell B6, Excel is just displaying the “hours and minutes” part of the answer. To specify your preference to display the entire time here, including the days, expressed in hours and minutes, you must use square brackets in the format. The number of hours and minutes in this sum will show in cell B6 if we apply the custom format of [h]:mm.
Remember when you work with time calculations in Excel:
1. consider how Excel is storing the time value in memory – as a serial decimal number and
2. make sure that the cell format displays the time as you intended