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

Comments

• Anonymous
  January 31, 2013
  Thanks for your postings, they're very helpful. How can I use the sum of the hours to calculate a total $ cost?  In other words, I want to multiply the total hours by a dollar amount to generate a job cost.

• Anonymous
  December 06, 2013
  I'm having same problem than Zorro. I need to calculate the cost of the total of the hour. Exemple: If i have 17:13:00 * 85$. If i do the sum, and format the cell as currency, it gives me 230.98$ wich is wrong.