Summing Times in hh:mm:ss in text

[azidrane]

I'm stumped.
I have a column that I'm trying to sum up. The column looks like this
A
1 11:59:37
2 18:13:29
3 12:48:39

Its in hh:mm:ss format and its pasted here in text, so its not an
actual value. Now when I do something like:
=A1+A2
I get the correct answer in the Excel decimal date format (1.259097222)
But if i do this:
=Sum(A1:A2)
I get a ZERO.
I can add as many as I want in this format:
=A1+A2+A3
and so on and it will continue to work. But the sum NEVER works.

Why, and more importantly, how do I fix it?

 

[Pete_UK]

Enter the value 1 into a blank unused cell somewhere, then select that
cell and click <copy>. Then highlight all the offending cells in column
A containing these times in text format and Edit | Paste Special |
Check Multiply in Operations section and click OK. This should change
all the times into numeric format, so that you can use SUM on them. You
can delete the 1 from the original cell.

Another way is to use =VALUE(A1) in a blank column and to copy this
down the column - the values can then be fixed using <copy> Edit |
Paste Special | Values (check) OK then <Esc>, and can then be used to
overwrite the original values.

Hope this helps.

Pete

 

[Bernie Deitrick]

azidrane,

Use

=SUM(A1:A2*1)

Entered with Ctrl-Shift-Enter

Of course, make the 2 as large as needed....

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

I copied and pasted you data and got 1.259097222 with both =A1+A2 and
=SUM(A1:A2)
Not sure what you mean by "decimal *date* format "; I assume you mean
General.
Send me file (my private email not to newsgroup) and I will see what I can
find.

 

[Dave Peterson]

If those values are really text--like the cell was formatted as text, then the
values were typed in--then I'd try to change the values to real times.

Select the range to fix (all of column A???)
edit|replace
what: : (just a colon)
with: : (that same colon)
replace all

Then check it with =sum(a:a)

 

[azidrane]

Perfect. The use of the Value() function is what got it working. I
think that both of the first ones would have worked but I chose the
value for my specific purposes. What i'm tyring to do is do a two
condition sum. The filed to sum is the time one that is in text so the
use of the value function worked perfectly.For anyone else you might
find this post, here is how you can use TWO condictions to sum a list
of times that are in text format:

=SUMPRODUCT(--(B1:B20="Condition One"),--(C120="Condition
Two"),VALUE(E1:E20))

Where column E contains data that is in text form, but can be in value
form. (ie 11:59:37 entered in text to represent time is noted by excel
as the value 0.499733796 whic means 0.499733796 of a day. A day in
excel is equal to 1, therefore any time value that is less then a day
is a decimal of 1 (ie 0.499733796))

Hope that makes sence. Thanks everyone for the help and for guiding me
to the correct statement.

Cheers!