The Excel file contains information on the time 6 cyclists took to complete a cycling contest. They are ranked in ascending order.

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

- Choose
**When calculating this workbook**. - Check
**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**.

**Step 2:**

- Use the
**Fill Handle**to drag the formula across the cells you want to fill.

Negative times are displayed in the Corrected Format column:

**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 will be displayed in**F6**.

**Step 2:**

- Enter the formula in the rest of the cells.

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

Negative times will be displayed in the Corrected Format column.

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