RE: Calculating Times

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • Re: Newbie problem: numbers with +positive and -negative indicators
    ... You need to fill the parentheses in the SUM() formula with the range you are ... If the cell is showing the formula as text, rather than the result, then ... like they are positive and negative numbers; how do I go into Format> ...
    (microsoft.public.excel.misc)
  • Re: results of "SUMIF" test criteria
    ... referenced cell is formatted using the general format. ... because I want to sum up data based format of the cell containing the data. ... the output of =Celldoes not change until I force a recalculation even though the sheet is set for automatic recalc. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Can sumif detect text strings or detect if a dollar sign $ is in a cell?
    ... Dim cell As Range ... Frank Kabel ... Jerry W. Lewis wrote:> You could write a VBA function that would check the format. ... >> If the value in your cells is a string with a $ sign manually entered>> you have to strip the number from this letter before you can sum>> them. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Can sumif detect text strings or detect if a dollar sign $ is in a cell?
    ... Dim cell As Range ... Frank Kabel ... Jerry W. Lewis wrote:> You could write a VBA function that would check the format. ... >> If the value in your cells is a string with a $ sign manually entered>> you have to strip the number from this letter before you can sum>> them. ...
    (microsoft.public.excel.misc)
  • Re: Problem with Find
    ... Dave wrote: ... Maybe the sum isn't really what you see in the cell--maybe it's formatted ... If the sum adds up to 7.99999, but the cell shows 8 because of formatting ... Then try clearing the Find format. ...
    (microsoft.public.excel.misc)