Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

migrated from stackoverflow.com Jan 30 at 13:27

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
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 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 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 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 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 at 11:53
show 18 more comments
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.