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.
📁 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.
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
- Select cell D6 and drag the Fill Handle icon from D6 to D11.
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.
- Then, we will go to the Options.
- 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.
- Now, go back to the worksheet and we can see that the negative time values are showing instead of ####.
📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel
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")
- Then, select cell D6 and drag the Fill Handle icon from cell D6 to D11.
Now, we can see the negative time values.
📕 Read More: 7 Examples to Calculate Duration of Time in Excel
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)
- Press the Enter key.
- Select cell D6 and drag the Fill Handle icon from cell D6 to D11.
📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
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)
- Press the Enter key.
- Select cell D6 and drag the Fill Handle icon from cell D6 to D11.
📕 Read More: How to Calculate Turnaround Time in Excel Excluding Weekends
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.
- Now, press the Enter key. Drag the Fill Handle icon from cell D6 to D11.
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.
- Now press the Enter key. Drag the Fill Handle icon from cell D6 to D11.
📄 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
- 4 Easy Ways to Calculate Percentage of Time in Excel
- How to Calculate Average Response Time in Excel
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- 2 Ways to Calculate Travel Time Between Two Cities in Excel
- How to Calculate Overtime and Double Time with Formula in Excel