6 Approaches to Subtract Time in Excel and Show Negative

When working with the time or date format in Excel, there is a chance that you may encounter a negative time value if you subtract the time value from another. If the cell includes a negative time or date value, Excel will display ##### instead of the proper value format. Today in this article, we are going to learn some easy ways to subtract and show negative time values in excel.

Here we will show you an overview of the whole article.

Overview of the Article


📁 Download Excel File

Download the Excel file here.


Learn to Subtract Time in Excel and Show Negative with These 6 Approaches

Here, we have a dataset of the Tokyo 2020 Athletics Men’s Marathon result. In the dataset, we can see that the Rank, Marathoner’s Name, and End Time are given. Now, we want to calculate the time difference between the other marathoners from the 1st marathoner. But now if we subtract the end time of the 1st marathoner and the 2nd marathoner normally it will return us “#####”. To solve this problem we are going to use the following 6 approaches.

Dataset for subtracting time in excel

approach

 1. Enable 1904 Date System to Show Negative Time

Here, we are going to use the 1904 Date System to display the negative values. First, we will subtract the end time of the other contestants from the end time of the first marathoners. Normally, it will return us a value like #####. Then we will change the date format to the 1904 Date System to display the negative values.

⬇️⬇️ STEPS ⬇️⬇️

  • In this worksheet, we have an additional column where we will calculate the time difference.
  • Select cell D6 and enter the following formula.
=$D$6-D6

Inserting a subtraction formula

  • Select cell D6 and drag the Fill Handle icon from D6 to D11.

Negative time values are not showing in the result

Here, we can see that we got the values as #####. Now we want to display the negative values.

  • Now, we will go to the Files section.

Opening Files segment

  • Then, we will go to the Options.

Opening Options section

  • From Options, we will select the Advanced option.
  • Then, scroll down to When calculating this workbook and we will select the Use 1904 date system option.
  • Finally, press OK.

Selecting 1904 date system from Excel Options

  • Now, go back to the worksheet and we can see that the negative time values are showing instead of ####.

Get the desired result using 1904 Date System

📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel

approach

2. Utilize IFFEROR Function to Subtract Time

In the second method, we will use the IFFEROR function. After evaluating a result if it finds an error, IFERROR returns the value you provide; otherwise, it returns the formula’s result.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell D6 and enter the following formula. Here, the function will evaluate the formula and if it finds any error it will return “Error”. Otherwise, it will return the result of the formula.
=IFERROR($D$6-D6,"Error")

Entering Formula based on the IFERROR function

  • Then, select cell D6 and drag the Fill Handle icon from cell D6 to D11.

Obtained result after using IFERROR function

Now, we can see the negative time values.

📕 Read More: 7 Examples to Calculate Duration of Time in Excel

approach

 3. Applying Excel MAX Function

In the third method, we will use the MAX function. The Max function returns the largest value from a set of values. But here we will provide only one value to the function and it will return the result of the formula.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell D6 and enter the following formula.
=MAX($D$6-D6)

Applying Formula based on the MAX function

  • Press the Enter key.
  • Select cell D6 and drag the Fill Handle icon from cell D6 to D11.

Drag the formula to get results for the remaining cells

📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

approach

4. Using MIN Function

In the third method, we will use the MIN function. The MIN function also works like the MAX function but instead of returning the largest value from a set of data, the MIN function returns the smallest value.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell D6 and then enter the following formula. Here instead of taking a set of data here, we will only provide the subtract formula to the MIN function and it will return the result of the formula.
=MIN($D$6-D6)

Insert formula based on the MIN function

  • Press the Enter key.
  • Select cell D6 and drag the Fill Handle icon from cell D6 to D11.

Get the result after applying the MIN function

📕 Read More: How to Calculate Turnaround Time in Excel Excluding Weekends

approach

5. Combination of IF and TEXT Functions

Here, we will use a combination of IF and TEXT functions. We will get the subtracted negative time in text format. So, here we will see that the results will take left alignment in the Excel sheet.

⬇️⬇️ STEPS ⬇️⬇️

  • In the first place go to cell D6 and enter the following formula.
=IF($D$6-D6>=0,TEXT($D$6-D6,"hh:mm:ss"),TEXT(ABS($D$6-D6),"-HH::MM::SS"))

🔨 Formula Breakdown

IF($D$6-D6>=0,TEXT($D$6-D6,”hh:mm:ss”),TEXT(ABS($D$6-D6),”-HH::MM::SS”))

👉  Here, the If function will perform a logical operation.

👉  If the $D$6-D6>=0 is true, it will return the subtraction $D$6-D6 in the “hh:mm:ss” format.

👉  And if the $D$6-D6>=0 is false, this will give us the absolute value of $D$6-D6 in this “-HH::MM::SS” format.

Inserting formula to subtract time and display negative values

  • Now, press the Enter key. Drag the Fill Handle icon from cell D6 to D11.

Obtained result in text form

approach

6. Show Negative Time in Text Format Combining TEXT, MAX, and MIN Functions

In the last method, we are going to use a combination of three functions that we already used in our previous methods. Here we will get the output in text format.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell D6 and enter the following formula.
=TEXT(MAX($D$6:D6)-MIN($D$6:D6),"-HH::MM::SS")

🔨 Formula Breakdown

TEXT(MAX($D$6:D6)-MIN($D$6:D6),”-HH::MM::SS”)

👉  MAX($D$6:D6), here MAX function will determine the largest value in the given range, which is 02:08:38.

👉  MIN($D$6:D6), MIN will return the smallest value in the given range which is 02:08:38.

👉  Now, it will subtract both values. Then pass them to TEXT function and the TEXT function will covert the subtracted value into text and display it in the “-HH::MM::SS” format.

Entering a formula based on the combination of the IF and TEXT function

  • Now press the Enter key. Drag the Fill Handle icon from cell D6 to D11.

Dragging fill handle icon to obtain result


📄 Important Notes

🖊️  In the 2nd, 3rd, and 4th methods we used the IFERROR, MAX, and MIN functions and got the negative time values. However, these functions only display negative time values only when the 1904 Date System is enabled in the workbook.


📝 Takeaways from This Article

📌  If we normally subtract two “time values” and if the result is negative Excel gives us the return as #######. But in this article, we learned how to display negative time values.

📌  Firstly, we used the 1904 Date System. This is a built-in option in Excel but we need to enable this first.

📌  Then we used some built-in functions. These functions returned a negative value instead of ######. These functions are IFERROR, MAX, and MIN.

📌  Then, we used the combination of TEXT and IF functions. This time we got the output in text format.

📌  Finally, we used the combination of TEXT, MAX, and MIN functions. Here also, we got the output in text format.


Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to subtract time in excel negative. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit ExcelDen to discover more about Excel.


Related Articles

(Visited 44 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo