{"id":11901,"date":"2022-10-05T08:10:22","date_gmt":"2022-10-05T08:10:22","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=11901"},"modified":"2024-03-26T12:32:51","modified_gmt":"2024-03-26T12:32:51","slug":"display-negative-time","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/","title":{"rendered":"Display Negative Time in Excel"},"content":{"rendered":"\n
Display-Negative-Time-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

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.<\/p>\n\n\n\n

One of the issues that you might find is that Excel does not display negative time<\/strong>. We will show how to fix this issue in the example below.<\/p>\n\n\n\n

Display 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

\"Graphical<\/figure>\n\n\n\n

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<\/strong> in this order, this is what we would end up with:<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

Race 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\n

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<\/strong> in Excel.<\/p>\n\n\n\n

To 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\n

When we click on it, we will have our negative time shown in the table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Display 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\n

TEXT 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

\"Graphical<\/figure>\n\n\n\n

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<\/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\n

Our 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

\"Graphical<\/figure>\n\n\n\n

As 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":"\nDisplay Negative Time in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Display Negative Time in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-05T08:10:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T12:32:51+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Display Negative Time in Excel\",\"datePublished\":\"2022-10-05T08:10:22+00:00\",\"dateModified\":\"2024-03-26T12:32:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\"},\"wordCount\":501,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\",\"name\":\"Display Negative Time in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png\",\"datePublished\":\"2022-10-05T08:10:22+00:00\",\"dateModified\":\"2024-03-26T12:32:51+00:00\",\"description\":\"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Display Negative Time in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Display Negative Time in Excel","description":"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/","og_locale":"en_US","og_type":"article","og_title":"Display Negative Time in Excel","og_description":"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.","og_url":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/","article_published_time":"2022-10-05T08:10:22+00:00","article_modified_time":"2024-03-26T12:32:51+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Display Negative Time in Excel","datePublished":"2022-10-05T08:10:22+00:00","dateModified":"2024-03-26T12:32:51+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/"},"wordCount":501,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/","url":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/","name":"Display Negative Time in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png","datePublished":"2022-10-05T08:10:22+00:00","dateModified":"2024-03-26T12:32:51+00:00","description":"Learn how to fix Excel's issue of not displaying negative time. Download the example file and follow the step-by-step guide on displaying negative time.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/10\/graphical-user-interface-table-excel-descriptio.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/display-negative-time\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Display Negative Time in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/11901"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=11901"}],"version-history":[{"count":3,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/11901\/revisions"}],"predecessor-version":[{"id":13989,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/11901\/revisions\/13989"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=11901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=11901"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=11901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}