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 2013/2016/2019/365.

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.

Table of Contents

## 1. 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.

## 2. 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.

## 3. Video: Display Negative Time Value

This tutorial video will demonstrate you how to** **display negative time value throught two methods (setting Excel options and Formula) in Excel 2013/2016/2019/365.

## 4. 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])…

## Leave a Reply

You must be logged in to post a comment.