Excel: Show and represent Negative times – Here’s

Excel can not show negative times when these were calculated by a formula. As a result of the garden fence (######) will be displayed. We show how you still calculate negative times in Microsoft Office Excel, view, and can represent.

Excel shows no results with negative times, but only the fence of diamonds.Excel shows no results with negative times, but only the fence of diamonds.

Excel: Show Negative times and represent - Here&# 8217; s

To display negative time in Excel, you make the following:

  1. In our example, we want to calculate: C3 = A3-B3. By an Excel error we get as a result ########## because the output is a negative term.
  2. Instead, we write the following formula in cell C3: = IF (A3 < B3;&# 8221; -&# 8221 ;;)&TEXT (ABS (A3-B3);&# 8221; hh: mm&# 8221;).
  3. The formula states: If cell A3 is smaller than cell B3, then to a &# 8220; -&# 8221; are output, and behind the magnitude of the difference A3-B3 in the format hh: mm. Amount means that the signs are omitted - also known as absolute.

The Excel spreadsheet now shows the negative time. However, this approach has a major drawback: The result - in our example, the cell C3 - is now formatted as text. You can therefore not expect the cell, otherwise the error #VALUE! is shown. But there is a solution to change that.

Excel can not be calculated, since the cell is C3 text and no number is the result. The value error appears.Excel can not be calculated, since the cell is C3 text and no number is the result. The value error appears.

Buy Office 2016 Home and Student | Amazon*

Excel: Expect negative times

To display negative time in Excel and be able to count on them, you make the following:

  1. Clicks in Excel on the File menu and select the menu item options.
  2. Click the left, click Advanced.
  3. Scroll down to the heading When calculating this workbook.

    Excel: Enables the 1904 date here.Excel: Enables the 1904 date here.

  4. Sets use a check mark at the 1904 date. Click OK.
  5. Now you can as usual calculate the formula. In our example we enter in cell C3: = A3-B3.
  6. The fence is gone and we can perform with the result in the cell C3 also further calculations.
Excel now expects negative times.Excel now expects negative times.

Note: Despite enabled 1904 date you can still enter any negative times directly into the cell in Excel, however. You can work around this by you solve the whole as in the screenshot above by formula.

More Articles Excel:

  • Excel spreadsheet turn - Here&# 8217; s
  • Convert Excel to PDF - Here&# 8217; s
  • Excel: characters are made easy

video | New in Excel 2016

9518New in Excel 2016

Did you "Excel: Show and represent Negative times - Here's" like Robert Lair?Write us in the comments or share the article.We look forward to your opinion - and of course you must like us on Facebook,twitter or Google+.

14.11.2017

Posted In: software

map