To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :
A1 = 02:30
A2 = 10:15
A3 has the formula: =SUM(A1:A2)
then A3 will have the correct values of 12:45.
However if your total times are greater than 24 hours then things get tricky.
If you have :
A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.
To retain the hours beyond 24, you have two alternatives:
1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)
2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.
In both cases the formula will remain as it is. Only the cell format differs.
See another example below:
More posts on time:
http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/
Related posts:
Thank you for your response, it was useful.
How do I add the following time:
Start Finished hrs wked
20:00 02:00
Please.
Thanks in advance
for all your help
Nana
Since your times span into two separate days then you have to include the day in your time.
The value of the cell will be something like this : 1/5/2010 20:29 , and the format like this: m/d/yyyy hh:mm
Then the formula for hrs wked will be : Finished – Start
Im trying to add 9000 cells of time formatted like hh:mm:ss and I can’t add them up. The data is coming from a server, converted to an excel spreadsheet and then cut and pasted with similar data. I cleared the formatting, reformated the cells with the above format and then tried to sum them, but the totaled cell looks like this “oo:oo:oo”. Any ideas on why I cant total time?
As explained above, if the total is greater than 24 hours you can not use straight sum. Try to sum just 2 or 3 cells and see if you get correct results. If the results are correct, then the problem is in your formula. Otherwise it may be a data problem.
If no luck I suggest that you upload a sample worksheet to Excel help form. Seeing the data will help people to identify the problem. Here is the link:
http://www.excelforum.com/
Your solution here is exactly what I was looking for.
Thank you much!
Hi Liza,
Using sum formula for adding hours is correct.
Try to change format to [hh]:mm:ss for hours more than 24hrs. It should work