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

[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.

[Barb Reinhardt]

SUM???
"Bridget" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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")

Bridget wrote:
>
> 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.

--

Dave Peterson

[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

"Dave Peterson" wrote:

> 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")
>
> Bridget wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>