Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Excel 2007 and I am trying to subtract these two values:


I have tried to format both as time and as "hh:mm:ss.000" and every time I try to subtract them I get #VALUE.

share|improve this question
up vote 1 down vote accepted

Works fine for me (Excel 2010). I suspect at least one of your times hasn't been parsed correctly into an Excel datetime.

Try to format your two times to 'General' format (Select them and press Ctrl + Shift + ~). If they don't then appear as numbers like below, then you have a parsing error - that is Excel isn't interpreting your input as timedates.

                hh:mm:ss.000    General
                ------------    -----------
Start time      11:27:55.428    0.477724861
End time        11:28:09.813    0.477891354
Difference      00:00:14.385    0.000166493

If you can't get the values to parse correctly when you enter them into the cell you can use the TIME() function to get the right values. For example:


share|improve this answer

I formatted cells A1,A2, and A3 = hh:mm:ss.000

A1 = 11:27:55.428
A2 = 11:28:09.813
A3 = A2-A1 = 00:00:14.385
share|improve this answer

Excel doesn't like negative date/times, so if you're subtracting the larger from the smaller then the result will not display correctly if time formatted (although you can just show it as General or a Number successfully).

Although, on my system (running 2007) it returns row of hashes instead if #VALUE, so this is probably not causing your problem, but worth keeping in mind when performing operations on time values.

share|improve this answer

After a long search, found this working for me.

share|improve this answer
While this may answer the question, it would be a better answer if you could provide some explanation why it does so. – DavidPostill Apr 29 at 11:13

You must log in to answer this question.

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