Suppose you have an Excel file that contains information about the time 6 cyclists took to complete a cycling contest. Cyclists were ranked in ascending order. The sheet has the Rank, Cyclistâ€™s name, Finish Time, Time Difference between two successive cyclists, and the Corrected Format to show the negative time after subtraction.

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

**Step 1:**

**â¦¿**Â Click the File tab.

**â¦¿** Click **Options**.

**Step 2:**

**â¦¿** Click **Advanced **options on the left pane.

**â¦¿** Scroll down the displayed window and choose **When calculating this workbook**. Tick **Use 1904 date system**.

**â¦¿** Click **OK**.

**â¦¿** Negative times will be displayed in the correct format.

**Read More:** How to Subtract Hours from Time in Excel

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

**Step 1:**

**â¦¿** Enter the following formula in **F6**.

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

**Formula Breakdown: **

**IF**will perform a**logical test**() to find out if the subtracted time value is positive. If the test returns*D5-D6>=0***TRUE**, no changes will be made.- If the test returns
**FALSE**, the function will first determine the absolute value of the subtracted time using the**ABS**feature. The**TEXT**feature will add a minus (-) in front of the subtracted time value usingas the text format.*“-H::MM”*

**â¦¿** Press **ENTER** and negative time will be displayed in **F6 **in the correct format.

**Step 2:**

**â¦¿** Use the **Fill Handle** to drag the formula across the cells you want to fill.

**â¦¿** All negative times in the Corrected Format column will be displayed in the right format.

**Read More: **How to Subtract Minutes from Time in Excel

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

**Step 1:**

**â¦¿** Enter the following formula in **F6**.

`=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 rangewhereas the*$D$5:$D$6***MIN**function will determine the smaller one in the same range. - The
**smaller**value in the absolute rangewill be*$D$5:$D$6***subtracted**from the**larger**value in that range. - The
**TEXT**function will then put a minus (-) in front of the subtracted time value usingas the text format.*“-H::MM”*

**â¦¿** Press **ENTER** and negative time in **F6** will be displayed in the correct format.

**Step 2:**

**â¦¿** Enter the formula for the rest of the cells in the Corrected Format column.

**Note:**We cannot drag the fill handle downwards, as we are using the absolute reference ($D$5:$D$6) for the range in both MAX and MIN functions. The formula must be entered in each cell in the column.

**â¦¿** All negative times in the Corrected Format column will be displayed in the right format.

**Read More: **How to Subtract 30 Minutes from a Time in Excel

**Quick Notes**

The **MAX** and **MIN** functions determine the largest and smallest values in a range. We are determining the larger and smaller values between two cells only. Therefore, cell reference must be absolute (** $D$5:$D$6**). Otherwise, both functions will throw

**errors**.

**Download Practice Workbook**

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

**Related Articles**

- How to Calculate Difference Between Two Times in Excel
- How to Calculate Time Difference in Numbers
- How to Calculate Time Difference in Minutes in Excel
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Subtract Military Time in Excel
- How to Subtract Time and Convert to Number in Excel
- How to Subtract Date and Time in Excel

**<< Go Back to Subtract Time | Calculate TimeÂ | Date-Time in Excel | Learn Excel**

Any chance that Microsoft will ever fix this bug?

I’ve looked all over, and keep finding these pseudo solutions / workarounds. None of that should be necessary.