Calculating Times
 

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

Calculating Times
 

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chris waller" wrote:

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

Calculating Times
 

Try something like

=SUM(--G1:G10)

--
__________________________________
HTH

Bob

"Chris waller" wrote in message
...
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

Calculating Times
 

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.

"Chris waller" wrote:

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