SUM function does not work correctly for time formatted data in table & pivot table

[chocolatedogs]

Using Excel 2016 for Win. I have time data in a column like 00:00:28 - if I use the SUM function =SUM(E9:E14) I get 00:00 in the calculated field. If I do the operation "long-hand" =E9+E10+E11+E12+E13+E14 I get the correct value.

I did not generate the data, so I thought some of it must be text. I have formatted all the time cells as [mm]:s to try to overcome this. I also copied and pasted special and toyed with the original time data and formatting of the original data in a hh:mm:ss format before adding/summing it - it is not being seen as a time formatted cell obviously, but I don't understand why the two methods get different answers.

I am using the time data in pivot tables too, and tried formatting as [hh]:mm or [mm]:ss as recommended around the 'net but it still gives me 00:00 as the total. I know there can be issues when times add to greater than 24 hours, but I thought the Custom formatting was to deal with this. I checked out the date grouping for Pivot tables but didn't think that was it.

I am going in circles trying to figure out the formatting issues!, and losing the little bit of mind I have left

Thanks for any help or feedback!

 

[Jonmo1]

The values must actually be text values.
Adjusting how the cell is 'Formatted' will not correct/change a Text Value to a Numeric Value.

How do the values orginally get into those cells ? Is it a formula?

E9+E10 works because any math operator like + - / * will coerce a text value into a number 'If it can'.
Basically anything that looks like it might be a number, is used as a number.

The SUM function doesn't do that.

 

[chocolatedogs]

The values must actually be text values.
Adjusting how the cell is 'Formatted' will not correct/change a Text Value to a Numeric Value.

How do the values orginally get into those cells ? Is it a formula?

E9+E10 works because any math operator like + - / * will coerce a text value into a number 'If it can'.
Basically anything that looks like it might be a number, is used as a number.

The SUM function doesn't do that.

Thanks so very much! It makes perfect sense now.

The data is "raw" data that I receive from another system that I can't change.

I am putting the data into a Pivot Table and need the sum of the times in the final answer. I know dates/times can be changed to decimal equivalents but I am not sure the best way to handle this in the most straightforward manner. Could you possibly direct me here also?

Thanks again a great, quick response!

I know date and time functions can be converted to other formats such decimal equivalents, but I need to see the final sum as time so I am not sure the most direct way to get there.

 

[Jonmo1]

Probably the easiest way to convert them to real time values

Copy Any blank cell
Higlight your range of Text Time Values (E9:E14)
Right Click - Paste Special - Values - Add - OK

 

[chocolatedogs]

Probably the easiest way to convert them to real time values

Copy Any blank cell
Higlight your range of Text Time Values (E9:E14)
Right Click - Paste Special - Values - Add - OK

Thanks again - I thought I had tried the Paste Special route, but obviously did not do it correctly. You save my sanity - very grateful.

 

[Jonmo1]

You're welcome.