total a time column that is formatted as ddd:hh:mm:ss .ddd=days

B

Bridget

Hi all, I am trying to total a time column. It is currently imported as
ddd:hh:mm:ss where ddd = days (not the day of the week). ANy ideas would be
appreciated.
 
D

Dave Peterson

I'd use data|text to columns to separate the ddd from the hh:mm:ss.

Then add the two columns and then sum them.

In B11:
=sum(B1:B10)+sum(c1:c10)

Then in B12:
=TEXT(INT(A11),"000")&":"&TEXT(MOD(A11,1),"hh:mm:ss")


Or don't use the data|text to columns and use these two formulas:

with data in A1:A10,
put this in A11:
=SUMPRODUCT(--LEFT(A1:A10,3))+SUMPRODUCT(--RIGHT(A1:A10,8))
And put this in A12:
=TEXT(INT(A11),"000")&":"&TEXT(MOD(A11,1),"hh:mm:ss")
 
B

Bridget

Dave,
This is getting me closer to an answer, but I don't have a solution
yet. I get an error when I use all your examples. But I like this sum product
idea and am investigating it further. Thanks
 
Top