If you are working with the time or date format in Excel and if you subtract a time value from another, then there is a possibility that you will come across a negative time value. If the cell contains a negative time or date value then Excel might show **##### **instead of the correct format of the value. In this tutorial, I will show you **how to subtract and display negative times in Excel**.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**3 Methods to Subtract and Display Negative Time in Excel**

Let’s assume a scenario where we have an Excel file that contains information about the time 6 cyclists took to complete a cycling contest. We have ranked the cyclist in ascending order from the winner of the contest to the last ranked cyclist. The worksheet has the** Rank**, name of the** Cyclist**, **Finish Time**, **Time Difference** between two successive cyclists, and the **Corrected Format** to show the **negative time** after **subtraction**. The image below shows the worksheet that we are going to work with.

**Method 1: Use the 1904 Date System to Subtract and Display Negative Time in Excel**

We can change the default date system to** the 1904 date system** to display the negative time after subtraction in the correct format. Follow the below steps.

**Step 1:**

**⦿** First, click on the** File** tab on the left of the** Home** tab.

**⦿** Now, a new window will open. Click on the **Options **like the image below.

**Step 2:**

**⦿ **Another window titled **Excel Options** will appear. Now, we will click on the **Advanced **option from the left pane.

**⦿ **A window with different advance options will now appear on the right side. Now, we will scroll down that window and find out **When calculating this workbook **section. Then, we will check the **Use 1904 date system** option.

**⦿ **Finally, we will click on the** OK** button.

**⦿ **If we now go back to the excel worksheet, we will see that the **negative times** are showing in the **correct format**.

**Read More: How to Subtract Time in Excel (7 Quick Methods)**

**Method 2: Apply the TEXT Function in Excel to ****Display Negative Time in Excel**

Alternatively, we can use the Excel** TEXT** function to display the negative time in the correct format. We have to follow the below steps.

**Step 1:**

**⦿** First, we will write down the below formula in cell **F6**.

`=IF(D5-D6>=0,TEXT(D5-D6,"mm:ss"),TEXT(ABS(D5-D6),"-H::MM"))`

**Formula Breakdown: **

- The
**IF**function will perform a**logical test**() to find out if the subtracted time value is positive. If the logical test returns*D5-D6>=0***TRUE**, the function will return the subtracted time value without changing anything. - And if the logical test returns
**FALSE**, the function will first determine the absolute value of the subtracted time using the ABS The**TEXT**function will then put a**negative sign**(**–**) in front of the**subtracted time value**usingas the text format.*“-H::MM”*

**⦿ **Now, upon pressing **ENTER**, we will see that the **negative time **in cell **F6** is showing in the **correct format**.

**Step 2:**

**⦿** Then, we will drag the** fill handle** to apply the formula to the rest of the cells.

**⦿** Finally, we will see that all the **negative times **in the **Corrected Format** column are shown in the **right format**.

**Read More:** **How to Subtract Hours from Time in Excel (2 Easy Ways)**

**Similar Readings**

**How to Calculate the Duration of Time in Excel (7 Methods)****Use Time Format in Excel VBA (Macro, UDF, and UserForm)****Excel Formula for Overtime over 40 Hours [with Free Template]****How to Sum Time in Excel (9 Suitable Methods)**

**Method 3: Using the Combination of TEXT, MAX, and MIN Formulas to Display Negative Time**

Along with the formula above, we can also the **TEXT** function with the **MAX** and** MIN** functions to create another formula that will also display the negative time values in the correct format.

**Step 1:**

**⦿** First, we will write down the below formula in cell **F6**. The formula is,

`=TEXT(MAX($D$5:$D$6)-MIN($D$5:$D$6),"-H::MM")`

**Formula Breakdown: **

- The
**MAX**function will determine the**larger**value in the absolute rangewhile the*$D$5:$D$6***MIN**function will determine the**smaller**one in the same range. - The
**smaller**value in the absolute rangewill then be*$D$5:$D$6***subtracted**from the**larger**value in that range. So, the subtracted time value will always be positive. - The
**TEXT**function will then put a**negative sign**(**–**) in front of the subtracted time value usingas the text format.*“-H::MM”*

**⦿ **Now, upon pressing **ENTER**, we will see that the **negative time **in cell **F6** is showing in the **correct format**.

**Step 2:**

**⦿ **Then, we will write the formula for the rest of the cells in the **Corrected Format** column.

**Note:**We can not drag the

**fill handle**downwards as we are using the absolute reference (

**for the range in both**

*$D$5:$D$6)***MAX**and

**MIN**functions. So, we will have to write the formula

**separately**for each cell in the column.

**⦿** Finally, we will see that all the **negative times **in the **Corrected Format** column are shown in the **right format**.

**Read More:** **How to Subtract Date and Time in Excel (6 Easy Ways)**

**Quick Notes**

🎯 The **MAX** and **MIN** functions respectively determine the** largest** and **smallest **values in a range. We are determining the larger and smaller value between only two cells. So, we have to make the cell reference absolute (** $D$5:$D$6**). Otherwise, both the functions will throw

**errors**as they expect the range to cover the

**other adjacent cells**in the same column.

🎯 You can read this article to learn how to subtract time in Excel.

🎯 Also read this one to learn how to subtract date and time in Excel.

**Conclusion**

In this article, we have learned how to **display negative time values after subtraction in Excel**. I hope from now on you can **display negative time value after subtraction in Excel** very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!

**Related Articles**

**How to Calculate Elapsed Time in Excel (8 Ways)****Excel Formula for Overtime over 8 Hours (4 Examples)****Formula to Calculate Hours Worked Minus Lunch****How to Calculate Average Response Time in Excel (4 Methods)****Add Hours to Time in Excel (8 Quick Ways)****Excel Formula To Calculate Time Worked****[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)****How to Calculate Time Difference in Excel (13 Ways)**