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
None of this works for me. I note that you show hh:mm, I cannot bring this up on my version of Excel, (Excel 97-2003 on Vista), either as ‘custom’ or as ‘Time’. The nearest I get in time is’13:30′, – this still does not work for me. Also I am wondering in your example you are summing only cells A1 to A5, and yet the column shows A1 to A6? So where do I go from here?
1. If any format is not available for you then you have to create a custom one. See this post for details:
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/
2. A1:A5 is a mistake. It should be A2:A6, and I have corrected that. Thanks for your observation.
OK. I’ve got EXcel 2007 and this use to work great on 2003.
I’ve got 800 lines of time to add up. The cells are formatted in TIME – HH:MM:SS. None of the cells are over 24 hours — i.e. it’s the largest is around 19 hours, 28 minutes and 30 seconds.
My formula is =sum(G11:G800) with the cell formated in CUSTOM [H]MM:SS.
It doesn’t work. I tried doing the =sum(G11:800)*24 and that didn’t work either.
What am I doing wrong?
What do you mean “It doesn’t work” ? What values did you get?
It doesn’t work. It comes up with a sum of 0 when I do the custom format. I can send the spreadsheet if you need it.
I did some scenarios and found out that the only way you get 0 is if the individual cells (not the cell for the total) are formatted as text not time. The data me look like 12:35:15, but internally it is not formatted as time. So please verify that and If necessary do Format –> Format Cells and chose the format hh:mm:ss. I hope this will help.
Hi, I would like to do this but with days. I have created a spreadsheet for a resource management game that I am playing, which forcasts how long it will take me to produce a desired amount of a resource. I would like the results to be displayed so that a result of 45 days would be returned as 45d rather that 1m 15d (or 2m 15d, as sometimes seems to happen).
The format I am currently using is d”d” h”h” m”m” and the formula is a basic C28/I26 where C28 is the target amount and I26 is production per hour.
I have tried [d]“d” h”h” m”m” but this does not get recognised. Any suggestions would be greatly appreciated
Also, for future reference, any ideas on how to get it to display 1m 15d rather than 2m 15d? I’m assuming its giving me the answer in the form of a date i.e. 15th of February rather than a measure of time.
Please note that I’m using 45d and 2m 15d as an example, its actually currently coming out 2m 14d because january is a 31 day month.
What are the values in C28 and I26 and what are the formats of those two cells ?
Currently,
C28= 9,110,000 (Represents Resource Target)
I26= 209,815 (Represents Production per Day (sorry, not per hour)
Both are formatted as a number, with 0 decimal places and include seperators.
The answer is currently 2m 12d 10h 03m
I’d like it to be displayed as 43d 10h 03m (or failing that; 1m 12d 10h 03m)
C28 and I26 must be formatted as Time for the formula to work properly.
THanks for the help.
OK. I’ve checked every cell — they are all formatted as Custom (H):mm:ss And it still does not work — but I am getting more than just 1 in the cell now.
So a +sum(g11:g650) shows (0):28:23.
Since one of the cells has 184 hours in it, I’m sure that’s not correct.
What am I doing wrong? I’m sure it’s an operator error.
Hour format is [H] not (H) !
Notice the brackets.
How can I add something like 10000:00 hrs(hh:mm) with smaller hh:mm and keep adding it??
Like I have columns A & B, A showing the hours something was used on a particular day and B showing total hrs it has been used. I want to add, fo eg. B2 = B1 + A2 and B3 =B2+A3 and so on.. B1 being any big value like 50000:00 or 100,000:00.
Please help.
1. Format column A as hh:mm
2. Format column B as [h]:mm
3. In B2 enter the formula =B1+A2
4. Autofill column B by dragging the fill handle.
Here is a couple of posts on autofill, in case you need more explanation:
http://www.exceldigest.com/myblog/2008/04/20/how-to-use-autofill-with-formulas/
http://www.exceldigest.com/myblog/2008/04/24/autofill-formulas-and-cells-references/