I have a simple case:
my j9-cell has formula: =h9-g9
when h9 has value 17:30 and g9 has value 17:00 j9 results 00:30 ... its ok!
but when h9 has value 16:30 it crashes..
Instead of showing -00:30 (as I expected) it shows a lot of #.
Am I missing something in formula? Thanks in advance.
Your calculation is producing a negative value, which constitutes an invalid date/time.
One way to fix it is to switch to the "1904 date system". To do this, select the "File" tab, then select "Options", then select "Advanced", then find the "Use 1904 date system" checkbox, and select it (this works when using Excel 2010 and newer).
Another way to fix it would be to change your formula to:
=IF(h9-g9<0, "-" & TEXT(ABS(h9-g9),"hh:mm"), h9-g9)
Warning: This produces a text value which may not be valid when used in other calculations.
What I would use:
This will generate the elapsed time as a text value, also if it's a negative result.
And since the result will always be a text, the format of the calculated field doesn't have to be changed to time.
Note: On a Dutch version of Excel the TEXT() formatting must be "u:mm" to make this work.
Excel does not appreciate negative time. You might want to incorporate a date into your calculations or possibly (maybe with a formula such as
=IF(H9>G9,H9-G9,1+G9-H9) format 'negative' time results in red with conditional formatting, or use fractions of a day instead of time.