Excel does not display negative time<\/strong>. We will show how to fix this issue in the example below.<\/p>\n\n\n\nDisplay Negative Time with Advanced Option<\/h2>\n\n\n\n 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:<\/p>\n\n\n\n <\/figure>\n\n\n\nAs 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<\/strong> in this order, this is what we would end up with:<\/p>\n\n\n\n <\/figure>\n\n\n\nRace in Bahrain (located in row 2)<\/strong> is the first race that was held, so we have nothing to compare it with. The second race, held in Saudi Arabia (row 3)<\/strong> was completed in less time (forget the reason why for this exercise). As seen, because the result of subtraction of race time<\/strong> from row 3 and row 2<\/strong> would give us the negative number, Excel leaves us with a bunch of hash marks.<\/p>\n\n\n\nThis 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<\/strong> in Excel.<\/p>\n\n\n\nTo do so, we need to go to the File tab >> Options >> Advanced >> scroll to the bottom >> In the \u201cwhen calculating this workbook\u201d part choose \u201cUse 1904 date system\u201d<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nWhen we click on it, we will have our negative time shown in the table:<\/p>\n\n\n\n <\/figure>\n\n\n\nDisplay Negative Time with Text Option<\/h2>\n\n\n\n To show the second option for displaying negative time in Excel, we will firstly uncheck the \u201cUse 1904 date system\u201d option.<\/p>\n\n\n\n
For this to work, we will use the TEXT option<\/strong>, in a combination with ABS<\/strong>. We will input the following formula in cell G2<\/strong>:<\/p>\n\n\n\n=TEXT(ABS(E3-E2),\"mm:ss\")<\/code><\/pre>\n\n\n\nTEXT formula<\/strong> has two parameters: value and format that we want to use for our value<\/strong>. Our value will be the absolute value (the number without its sign) of the subtraction result of two race\u2019s times<\/strong>, and our format will be minutes and seconds<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe 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<\/strong>. This is what our changed formula will look like:<\/p>\n\n\n\n=IF(E3-E2>=0,TEXT(E3-E2,\"mm:ss\"),TEXT(ABS(E3-E2),\"-mm:ss\"))<\/code><\/pre>\n\n\n\nOur IF formula<\/strong> will check if the value of subtracted numbers is higher or equal to 0<\/strong>. If so, then it will show a positive value. If not, then the negative value will be shown, in \u201cmm:ss\u201d<\/strong> format<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nAs shown in row number nine, we now have negative time values shown as well.<\/p>\n","protected":false},"excerpt":{"rendered":"
When you deal with Excel, a lot of times you can come across unusual issues that can seem to be unsolvable. Luckily, there…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Display Negative Time in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n