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.

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 time1

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.

display negative time2

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

display negative time3

#3 the negative time will be shown normally.

display negative time4

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.

display negative time5

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

Related Functions


    • Excel Text function
      The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
    • Excel MIN function
      The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
    • Excel MAX function
      The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar