Calculating Times

[Chris waller]

I have been helping a colleague with an Excel problem and to be honest it has
got me stumped. My colleague is downloading a report from one system into an
Excel spreadsheet. A number of the columns contain time in the format
hh:mm:ss, however if you try to use the autosum or the = sum formula on them
and subsequently format the answer cell it shows 00:00:00, however if you go
into each cell edit (F2) and then press enter, it then includes the figures
in the total. As this could be a very laborious job depending how many cells
there are, I was wondering if anyone knew what was causing the problem and a
possible answer. I do have an abridged version of the file with me, so if
anyone needs to look at it, I could post to a convenient place. TIA

 

[Luke M]

It sounds like the download is inputting the time as text, instead of a
serial time value. Thus, when you use SUM, you get the value of 0 (SUM
ignores text).
A quick workaround if you expect future downloads might be to have a helper
column with this formula:
=TIMEVALUE(A2)
and then base your calculations off of this.

The other alternative it to input the value of 1 into a cell, copy that
cell, and then do a Paste Special - Multiply against all your times to force
them to become numbers.

 

[Bob Phillips]

Try something like

=SUM(--G1:G10)

 

[berniean]

In my experience, when a column is text but should be numbers (including
dates), using Data/Text to Columns...menu works easiest. Select the column.
Click the Data menu. Choose Text to Columns and follow the prompts. When you
get to the "Column data format" choices, you can choose General or Date (in
several formats). I use this method to reformat dates stored as text in
Oracle and downloaded to Excel through a BI reporting tool.