Excel 2007 time miliseconds operations

Question

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.

Answer 1

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)

Answer 2

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

Answer 3

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.