In an earlier tip Calculating elapsed time in Excel, I shared a quick and easy formula for determining the difference between two times. The advantage of that approach is that it doesn’t require you to format the cells. The downside is – as Nicky, one of our readers, noted in comments – that you’re converting the result to text, so you cannot add your "Number of hours" calculations using the SUM function:
If you want to be able to SUM your results, there’s a better way. First, calculate the elapsed time using a standard subtraction function, like this:
Now, instead of the expected 2:30:00, you’ll probably get something like 0.104167 – which isn’t particularly useful. Or you might get 2:30:00 AM, which is better – but the use of AM isn’t really helpful either.
In either case, right click the cell, select Format Cells, click Custom, and choose [h]:mm:ss and then OK:
This will convert the result to hours, minutes, and seconds. That’s more like it, right?
The reason you want the one with the [h] is because this prevents the totals from "rolling over" if you exceed 24 hours. For instance, 26 hours would become 2 hours if you selected hh:mm:ss or one of the many other time formats that do not start with [h].