Time data not read as time format

[mayday1]

I inherited an Excel 2007 file that has a column of times in hh:mm:ss format. At least that's the way it appears. If I click on one of the cells and look at format it shows its in 37:30:55 format. But, I can't add up the times. A =SUM of all the cells in the column shows to be 0:00:00. What makes it weirder is that if I click on a cell and then click on the value of the cell in the formula bar then hit enter, the cell is now treated as a time and I can sum up the times. I don't want to do that for 20,000 rows. How can I get all othe cells to add up and what might be causing this?
Thank you

 

[VoG]

Try this: select the times, Data > Text to Columns, tick Delimited and click Finish.

 

[schielrn]

Does this work?

=SUM(A1:A10+0)

This needs to be confirmed with control+shift+enter. If so then they are not formatted as a number and you may need to just select the column and either use data-->text to columns to convert them to numbers or there may be a little exclamation point drop down that comes up allowing you to change them.

Hope that helps.

 

[mayday1]

Try this: select the times, Data > Text to Columns, tick Delimited and click Finish.


This worked. Thank you very much.
Do you know why I had to do this?

 

[VoG]

The dates were stored as Text.