Summing day and time amounts

Posted by liz on November 21, 2001 5:41 AM

I have a set of cells copied from a database containing day and time amounts in the custom format dd:hh:mm:ss i.e. 1 day, 2 hours, 34 minutes and 16 seconds would be 01:02:34:16.
I need to add these amounts up and produce a total at the bottom of the worksheet, any ideas anyone?

Posted by Mark W. on November 21, 2001 6:18 AM

Suppose that cells A1:A2 contains...

{"01:02:34:16";"16:04:05:22"}

...and you wish to provide a total in cell A3.

1. Enter =LEFT(A1,2)+RIGHT(A1,8) into cell B1 and
copy down to B2.
2. Enter =SUM(B1:B2) into cell B3.
3. Enter =TRUNC(B3)&":"&TEXT(MOD(B3,1),"hh:mm:ss")
into cell A3.
4. Hide column B (if desired).

Posted by liz on November 21, 2001 6:58 AM

Thanks Mark, that's a great help