If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.
Example 1 : total hours less than 24.
Cells B1, B2, B3 and B4 contains the times.
Cell B5 (total) contains the formula:
=SUM(B1:B4)
The result of this sum is 23:05 (23 hours and 5 minutes).
Please note that cells B1to B5 are formatted as time (hh:mm).
Example 2 – total hours greater than 24.
Cells B1, B2, B3 and B4 contains the times.
The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).
To avoid this problem we use the following formula instead: =SUM(B1:B4)*24
We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).
Applies to Excel 2003
Related posts:
What do you do if you have
B1 21:36:41
B2 2:51:46
B3 3:20:00
Total
B4 3:48:27
When I should be around 26 Hours total
The second example explains how to sum times when the total is greater than 24 hours.
What to do if B1 value is greater than 24h, if it is say 121:24:14 (hh:mm:ss)
Format the cell with this custom format :
[h]:mm:ss
See this post for more details:
http://www.exceldigest.com/myblog/2009/11/29/how-to-some-times-greater-than-24-hours-in-excel-2007/
In the end it does work, but after formatting all the cells like that I had to double-click on each and every one
and after that I ended up with some cells with values like this 1/5/1900 05:25:31 instead of 125:25:31…
Imagine 1000+ entries that you have to double-click, and\or change back… very time consuming…
I will stick to plain and simple formulas like =A1+A2+A3…
for now
Thank you never the less.
You don’t have to format cells one by one. Just format one cell and then use autofill for the rest:
http://www.exceldigest.com/myblog/2008/03/31/automatically-fill-data-in-adjacent-cells-autofill/
http://www.exceldigest.com/myblog/2008/07/26/how-to-perform-autofill-without-dragging-the-fill-handle/
Many thanks, this did answer my problem of adding working hours.
My pleasure!.