Subtracting time gives "####" when it should be negative time

Question

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.

Answer 1

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.

Answer 2

my favourite solution is to display time as an integer when negative only, applying the following custom format:

[h]:mm:ss;-#.##0,##;0;@

this allows to do all required calculations on timestamps without having to resort to cumbersome conversions, and works just as fine with formulas, pivot tables and others.

more about custom format syntax: https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68