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:

Two ways to add times

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:

Format Cells

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


Comments (2)

  1. Zorro says:

    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.

  2. Jeff25 says:

    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.