In excel, when I work with hour, and I need for instance to know the duration of an issue, excel may show a negative time but instead of that, it shows something like “######”.

There are different ways to resolve and based on what I need to do, one is better than the other. Let’s see the different options.

Option 1

Active the function “use 1904 date system”, to do that, go to “file -> options -> advanced” then search for “when calculating this workbook”. If you have many excel files open, make sure to select the right one before selecting this option (it will only apply for this file).

The only thing for this option is that I can get an error if I have other pivottables or tables because it will recalculate the whole file and some may not work properly. To remediate, if you know which one, just recreate it, if not, recreate all of them. The other solution, create a new file and start from 0.

Option 2

Use this formula:

=TEXT(MAX(A2:B2)-MIN(A2:B2),"-[h]:mm")

But it will always show a negative value.

Option 3

Use this formula:

=IF(B2-A2<0,"-"&TEXT(ABS(B2-A2),"[h]:mm"),B2-A2)

Then format the cell with “[h]:mm”. Comparing to the option 2, this one allows to show a positive and negative hour.

NOTE

• Formatting the cell with “[h]:mm” instead of “h:mm”, it will allow to display the hour above 24h as a time value
• For option 2 and 3, the formula will return as a text value instead of a numeric value. To convert it into decimal number:
  =SUBSTITUTE(D2,"-","")*24*SIGN(LEFT(D2,2)+0)