# How to Subtract and Display Negative Time in Excel – 3 Methods

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:

• 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 (D5-D6>=0) to find out if the subtracted time value is positive. If the test returns 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 using “-H::MM” as the text format.

• 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:

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

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

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

1 Comment
1. 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.

Advanced Excel Exercises with Solutions PDF