Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

11:27:55.428
11:28:09.813

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
add comment

3 Answers

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:

=TIME(11,27,55.428)

share|improve this answer
add comment

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
add comment

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
add comment

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.