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.
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:
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
I formatted cells A1,A2, and A3 = hh:mm:ss.000