0

I have log from a tool in this format: hh:mm:ss.uuu. For example: 10:12:34.323

Setting these cells in Excel 2003 to time format with syntax hh:mm:ss.000 is accepted, but any operation, like add/subtract, yelds to #Value! error.

Is there any way for Excel to do calculation on already set custom and recognized time format, without extra effort?

Update with screenshots:

Format Cells

Evaluation

Improve this question

2 Answers 2

Reset to default
3

I'd expect calculations like

=A1+B1

to work OK - even if your data is text formatted - the addition should "co-erce" to a valid time value - perhaps you have extra characters in the data? What do you get with

=LEN(A1)

where A1 contains one of your time values - it should be 12

Improve this answer
23
  • Are you saying it works for you fine? If that's so, can you tell me which Excel version are you running? I updated my question with screenshots. There are no extra chars in data
    – zetah
    Jan 30, 2013 at 10:40
  • Yes, it works for me using Excel 2007 - if the data is numeric or text - Excel 2003 should be no different. It looks like your data is text - try using "text to columns" to convert - select column of times and just use Data > Text to columns > Finish - does that make any difference - do calcs work after that?
    – barry houdini
    Jan 30, 2013 at 11:22
  • Well, you can see in formula bar and then in "Format Cell" sample box, from first screenshot that data is not text. Analogously your "Text to column" suggestion does not make difference.
    – zetah
    Jan 30, 2013 at 11:34
  • Excel will let you set a number format to any cell, even if that cell contains text, so if I type "cat" into cell D1 I can still set the format of that cell to mm/dd/yy and the cell will still display "cat" - so just because the format of A1 is set to hh:mm:ss.000 that doesn't mean that the cell isn't text formatted - if you have a text formatted cell then you can't stop it being text by changing the number format - if you change the number format to general does that change how the data is displayed? If not then you have text - also test with ISNUMBER(A1)
    – barry houdini
    Jan 30, 2013 at 11:42
  • As using "Text to column" with "general" format, doesn't change current state, do you maybe have any other suggestions?
    – zetah
    Jan 30, 2013 at 11:53
2

You should check that the formats were actually applied to the cells. In your screenshot you have double quotes around your values so they appear to just be strings. If they were being properly interpreted I believe you would see something like 0.43836... for the dates instead of "10:31:16.465" in the formula evaluation window.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .