How to Subtract and Display Negative Time in Excel (3 Methods)

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.


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.

How to Display Negative Time Value After Subtraction in Excel


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.

1904 Date System

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

Use the 1904 Date System to Subtract and Display Negative Time in Excel

Step 2:

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

1904 Date System

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

Use the 1904 Date System to Subtract and Display Negative Time in Excel

 

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

Use the 1904 Date System to Subtract and Display Negative Time in Excel

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

TEXT Function in Excel

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

Apply the TEXT Function in Excel to Display Negative Time in ExcelUsing the Combination of TEXT, MAX, and MIN Formulas to Display Negative Time

Step 2:

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

Apply the TEXT Function in Excel to Display Negative Time in ExcelUsing the Combination of TEXT, MAX, and MIN Formulas to Display Negative Time

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

Apply the TEXT Function in Excel to Display Negative Time in ExcelUsing the Combination of TEXT, MAX, and MIN Formulas to Display Negative Time


Similar Readings:


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 range $D$5:$D$6 while the MIN function will determine the smaller one in the same range.
  • The smaller value in the absolute range $D$5:$D$6 will then be 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 using “-H::MM” as the text format.

Combination of TEXT, MAX, and MIN Formulas

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

 Using the Combination of TEXT, MAX, and MIN Formulas to Display Negative Time

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 ($D$5:$D$6) for the range in both 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.

Corrected Format column

Related Content: 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

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo