How to Display Negative Time Value in Excel

 This post will guide you how to display negative time value in Excel. How do I change the way negative times are displayed in Excel. How to show negative dates or times with a formula in Excel.

• Display Negative Time Value
• Display Negative Time Value with Formula
• Video: Display Negative Time Value

If the cell value is a negative time or date value, and the Excel will only show #### error in that cell. So how to correct this error to show the correct negative time or date.

Display Negative Time Value

---

You can change the Excel’s Default Date System to correct this error. Just do the following steps:

#1 click File tab, and select Options from the menu list. And the Excel Options dialog will open.

#2 click Advanced from the left pane, and check Use 1904 data system under When calculating this workbook section. Click Ok button.

#3 the negative time will be shown normally.

Display Negative Time Value with Formula

---

You can also use a formula based on the Text function, the Max function and the MIN function to achieve the result of displaying negative time value properly in Excel.

Just like this formula:

=TEXT(MAX($B$1:$B$2)-MIN($B$1:$B$2),"-H::MM")

Type this formula into a blank cell and then press Enter key in your keyboard.

You will see that a negative time value will be displayed in that cell.

The combination of the MAX function and the MIN function will subtract the earlier time from the later time, and get the positive time value. And then it will be formatted using the TEXT function so that it shows a negative time.

Note: you should know that the returned result is actually a text value, not a numeric value.

Video: Display Negative Time Value