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

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.

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.