When working with the time or date format in Excel, there is a chance that you may encounter a negative time value if you subtract the time value from another. If the cell includes a negative time or date value, Excel will display** #####** instead of the proper value format. Today in this article, we are going to learn some easy ways to subtract and show negative time values in excel.

Here we will show you an overview of the whole article.

## 📁 Download Excel File

Download the Excel file here.

## Learn to Subtract Time in Excel and Show Negative with These 6 Approaches

Here, we have a dataset of the **Tokyo 2020 Athletics Men’s Marathon** result. In the dataset, we can see that the** Rank, Marathoner’s Name,** and **End Time** are given. Now, we want to calculate the time difference between the other marathoners from the **1st **marathoner. But now if we subtract the end time of the **1st marathoner** and the **2nd** marathoner normally it will return us **“#####”**. To solve this problem we are going to use the following **6** approaches.

**approach**

### 1. Enable 1904 Date System to Show Negative Time

Here, we are going to use the **1904 Date System** to display the negative values. First, we will subtract the end time of the other contestants from the end time of the first marathoners. Normally, it will return us a value like **#####**. Then we will change the date format to the **1904 Date System** to display the negative values.

**⬇️⬇️ STEPS ⬇️⬇️**

- In this worksheet, we have an additional column where we will calculate the time difference.
- Select cell
**D6**and enter the following formula.

`=$D$6-D6`

- Select cell
**D6**and drag the**Fill Handle**icon from**D6**to**D11**.

Here, we can see that we got the values as **#####**. Now we want to display the negative values.

- Now, we will go to the
**Files**section.

- Then, we will go to the
**Options**.

- From
**Options**, we will select the**Advanced**option. - Then, scroll down to
**When calculating this workbook**and we will select the**Use 1904 date system**option. - Finally, press
**OK**.

- Now, go back to the worksheet and we can see that the negative time values are showing instead of
**####**.

**📕 Read More: ****9 Tricks to Calculate Time Difference Between AM and PM in Excel**

**approach**

### 2. Utilize IFFEROR Function to Subtract Time

In the second method, we will use the **IFFEROR** function. After evaluating a result if it finds an error, **IFERROR** returns the value you provide; otherwise, it returns the formula’s result.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select cell
**D6**and enter the following formula. Here, the function will evaluate the formula and if it finds any error it will return “**Error**”. Otherwise, it will return the result of the formula.

`=IFERROR($D$6-D6,"Error")`

- Then, select cell
**D6**and drag the**Fill Handle**icon from cell**D6**to**D11**.

Now, we can see the negative time values.

**📕 Read More: ****7 Examples to Calculate Duration of Time in Excel**

**approach**

### 3. Applying Excel MAX Function

In the third method, we will use the **MAX** function. The** Max **function returns the largest value from a set of values. But here we will provide only one value to the function and it will return the result of the formula.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**D6**and enter the following formula.

`=MAX($D$6-D6)`

- Press the
**Enter**key. - Select cell
**D6**and drag the**Fill Handle**icon from cell**D6**to**D11**.

**📕 Read More: ****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends**

**approach**

### 4. Using MIN Function

In the third method, we will use the **MIN** function. The **MIN** function also works like the** MAX **function but instead of returning the largest value from a set of data, the **MIN** function returns the smallest value.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6**and then enter the following formula. Here instead of taking a set of data here, we will only provide the subtract formula to the**MIN**function and it will return the result of the formula.

`=MIN($D$6-D6)`

- Press the
**Enter**key. - Select cell
**D6**and drag the**Fill Handle**icon from cell**D6**to**D11**.

**📕 Read More: ****How to Calculate Turnaround Time in Excel Excluding Weekends**

**approach**

### 5. Combination of IF and TEXT Functions

Here, we will use a combination of** IF** and **TEXT** functions. We will get the subtracted negative time in text format. So, here we will see that the results will take left alignment in the Excel sheet.

**⬇️⬇️ STEPS ⬇️⬇️**

- In the first place go to cell
**D6**and enter the following formula.

`=IF($D$6-D6>=0,TEXT($D$6-D6,"hh:mm:ss"),TEXT(ABS($D$6-D6),"-HH::MM::SS"))`

**🔨 Formula Breakdown **

**IF($D$6-D6>=0,TEXT($D$6-D6,”hh:mm:ss”),TEXT(ABS($D$6-D6),”-HH::MM::SS”))**

👉 Here, the **If** function will perform a logical operation.

👉 If the **$D$6-D6>=0 **is true, it will return the subtraction **$D$6-D6 **in the **“hh:mm:ss” **format.

👉 And if the **$D$6-D6>=0 **is false, this will give us the absolute value of **$D$6-D6 **in this **“-HH::MM::SS” **format.

- Now, press the
**Enter**key. Drag the**Fill Handle**icon from cell**D6**to**D11**.

**approach**

### 6. Show Negative Time in Text Format Combining TEXT, MAX, and MIN Functions

In the last method, we are going to use a combination of three functions that we already used in our previous methods. Here we will get the output in text format.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6**and enter the following formula.

`=TEXT(MAX($D$6:D6)-MIN($D$6:D6),"-HH::MM::SS")`

**🔨 Formula Breakdown**

**TEXT(MAX($D$6:D6)-MIN($D$6:D6),”-HH::MM::SS”)**

👉 **MAX($D$6:D6), **here **MAX** function will determine the largest value in the given range, which is **02:08:38**.

👉 **MIN($D$6:D6)**,** MIN** will return the smallest value in the given range which is **02:08:38**.

👉 Now, it will subtract both values. Then pass them to **TEXT** function and the **TEXT** function will covert the subtracted value into text and display it in the **“-HH::MM::SS” **format.

- Now press the
**Enter**key. Drag the**Fill Handle**icon from cell**D6**to**D11**.

**📄 Important Notes**

🖊️ In the **2nd**, **3rd**, and **4th** methods we used the **IFERROR**, **MAX**, and **MIN** functions and got the negative time values. However, these functions only display negative time values only when the **1904 Date System** is enabled in the workbook.

## 📝 Takeaways from This Article

📌 If we normally subtract two “time values” and if the result is negative Excel gives us the return as **#######**. But in this article, we learned how to display negative time values.

📌 Firstly, we used the **1904 Date System**. This is a built-in option in Excel but we need to enable this first.

📌 Then we used some built-in functions. These functions returned a negative value instead of **######**. These functions are** IFERROR**, **MAX**, and **MIN**.

📌 Then, we used the combination of **TEXT** and** IF** functions. This time we got the output in text format.

📌 Finally, we used the combination of **TEXT**, **MAX**, and **MIN **functions. Here also, we got the output in text format.

## Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to subtract time in excel negative. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit **ExcelDen** to discover more about Excel.

## Related Articles

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****4 ways to Create Excel Formula for Overtime Over 8 Hours****2 Ways to Calculate Travel Time Between Two Cities in Excel****How to Calculate Overtime and Double Time with Formula in Excel**