Adding times together in Excel

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:

=C2-B2

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].

Suzanne