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)**