When you deal with Excel, a lot of times you can come across unusual issues that can seem to be unsolvable. Luckily, there is, almost always, a solution for every problem.
One of the issues that you might find is that Excel does not display negative time. We will show how to fix this issue in the example below.
Display Negative Time with Advanced Option
For our example, we will use the list of races in Formula 1 that happened during 2022 (some of them), with the names of the winners, the venues, and the time that took for every winner to win the race:
As you might notice, the races are ordered in chronological order, and the times of the races are different. If we want to simply calculate the difference in times from races, by subtracting one from another in this order, this is what we would end up with:
Race in Bahrain (located in row 2) is the first race that was held, so we have nothing to compare it with. The second race, held in Saudi Arabia (row 3) was completed in less time (forget the reason why for this exercise). As seen, because the result of subtraction of race time from row 3 and row 2 would give us the negative number, Excel leaves us with a bunch of hash marks.
This means that Excel cannot show negative time at this point. To change this, we have two options. The first option is to change the date system in Excel.
To do so, we need to go to the File tab >> Options >> Advanced >> scroll to the bottom >> In the “when calculating this workbook” part choose “Use 1904 date system”:
When we click on it, we will have our negative time shown in the table:
Display Negative Time with Text Option
To show the second option for displaying negative time in Excel, we will firstly uncheck the “Use 1904 date system” option.
For this to work, we will use the TEXT option, in a combination with ABS. We will input the following formula in cell G2:
1 |
=TEXT(ABS(E3-E2),"mm:ss") |
TEXT formula has two parameters: value and format that we want to use for our value. Our value will be the absolute value (the number without its sign) of the subtraction result of two race’s times, and our format will be minutes and seconds:
The problem with this formula is that it now does shows numbers, but they are all positive. To show the negative numbers as well, we need to use them in a combination with the IF formula. This is what our changed formula will look like:
1 |
=IF(E3-E2>=0,TEXT(E3-E2,"mm:ss"),TEXT(ABS(E3-E2),"-mm:ss")) |
Our IF formula will check if the value of subtracted numbers is higher or equal to 0. If so, then it will show a positive value. If not, then the negative value will be shown, in “mm:ss” format:
As shown in row number nine, we now have negative time values shown as well.