While calculating difference in your worksheet, you may require subtracting two time values. If you try subtracting a time elapsed earlier from a recent time, you will not be displayed with a negative value as expected.Instead, Excel displays to you a series of hash symbols (#). This generally happens because Excel, for Windows users by default uses the 1900 date system. You can fix this unwanted problem.
-
Start “Excel 2007” and click the “Office button”.
-
Click the “Excel Options” button.
-
In the “Excel Options” dialog box that appears, select the “Advanced” option, on the left panel.
-
Scroll down till you reach the “When calculating this workbook:” section.
-
Check the “Use 1904 date system” option.
-
Click “OK” to save the changes.
-
Your time will now be displayed along with a minus (-) sign. The 1904 date system is used as the default date system for MAC users of Excel.
If you do not wish to change the default date system for Excel your machine, you can convert the value
into a text equivalent. To do so carry out the following steps:
-
Presume cell B2 has the earlier time and cell A2 a recently lapsed time.
-
Go to the destination cell where the difference should appear.
-
Type the formula as “ =IF(B2-A2<0, “-” & TEXT(ABS(B2-A2),”hh:mm”), B2-A2)”
As per this formula, you are using three functions, that is, IF(), TEXT() and the ABS() function. The IF condition checks whether the resultant value is a negative number or not.
If the function returns a true value the ABS() function converts the value to a positive number and we affix a minus (-) symbol to signify the negative value.
The TEXT() function, converts the result to a text value. The “&” is used to concatenate two text values.
If the IF() function returns false, the difference of the two values will be displayed as it is.
This reply was very precise and helped me a lot I searched a lot but was unable to find the solution on how to display a negative hour on the worksheet.
Thank you v much for posting.