Click here to Skip to main content
15,919,931 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an need to add a column of Times in excel resulting in the accumlated time as a total. I tried to SUM the column but if the result is > than 24 hours I do not get the right answer, obviously because the time is actually stored as a DateTime value.

Any one offer a solution, I would be most grateful.
Posted

Actually, setting the format of the cell to "Custom" -> "[h]:mm:ss;@" will work out much simpler.

See here:
http://support.microsoft.com/kb/214094[^]
 
Share this answer
 
Comments
Steven J Jowett 24-Aug-11 4:08am    
Well that's much better than my solution. 5* mate thanks.
OK - I've solved it:

formula is as follows:

=ROUNDDOWN(SUM(A1,A2)*24, 0) & MID(TEXT(SUM(A1,A2), "hh:mm:ss"),3,6)

Resulting in a text cell something like this:

29:51:23

ie 29 hrs, 51 mins, 23 secs
 
Share this answer
 
Comments
Philippe Mori 23-Aug-11 19:08pm    
Solution 2 is much better. You don't need any formula in the cell. Just apply appropriate formatting.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900