Have one more Time issue

[TroubleMaker]

Hey All,

Here is my next time issue. I am trying to add a list on hh:mm:ss in a column to get a total time. I have my total cell formatted for [h]:mm:ss and the formula of "=TIMEVALUE(O2:O38)" and I am getting #VALUE in the cell where the total is displayed. If I just try to add these cells in the column I get "00:00:00". However if I go in and type "=(O2+O3+O4+etc...)" they start adding the time in the cell. Can you help me out please?

[Mark W.]

Select your "list of hh:mm:ss" values, choose the Data | Text to Columns... wizard, and press [ Finish ].

Now, you can total your times using...

=SUM(O2:O38)

[ This Message was edited by: Mark W. on 2002-05-13 09:26 ]

[TroubleMaker]

Thanks... however that did not work. Still getting "00:00:00". Any other suggestions?

[Dave Patton]

try =Sum(O2:O38)

[Asala42]

Hiya Trouble,

Feel free to kick me an email and I'll take a look at the file. - you may want to tweak the file or even make a test file if you've got any confidential info.

asala42@yahoo.com

Adam

[TroubleMaker]

Hey Maybe this will help,

I have 2 columns that I figured out the difference in time:
"J2 & K2"
"2/25/02 13:00:07 & 2/25/02 13:35:46"

I have that going to "O2" thru "O38" with the following:
"=INT(SUM(K2-J2)/0.04166667)&":" &MINUTE(MOD(SUM(K2-J2),1))&":" &SECOND(MOD(SUM(J2-K2),1))"

Now I am trying to get a "total time" from that columns "O2 thru O38" by using:
"=SUM(O2:O38)"
I place this formula in the "O40" cell with the cell formatted to:
"[h]:mm:ss"
Getting the result of:
"0:00:00"

If I place this into "O40":
"=SUM(O2+O3+O4 etc..."
It WORKS!! And will add the columns and give me a total.

I have a slight problem however... This is going to total 50,000 lines and Excel will not let me (thank God) type all of those cells in.

I tried to do the "Data/Column to text" method as stated above with the =SUM(O2:O38) with no effect.

Still looking for a solution... Maybe the full story helps with the problem.

[Asala42]

Oh I think I got it,

Try:

=Sumproduct((O2:O38)+0)

Adam

[TroubleMaker]

That did it!!! YES!!!

Thanks a ton...

Dan

[Mark W.]

On 2002-05-13 10:25, TroubleMaker wrote:
Thanks... however that did not work. Still getting "00:00:00". Any other suggestions?

The following works fine...

1. Select your "list of hh:mm:ss" values
2. Choose Data | Text to Columns...
3. Press [ Finish ].

Now, you can total your times using...

=SUM(O2:O38)


Try it again!