{"id":1211,"date":"2015-05-05T15:40:40","date_gmt":"2015-05-05T13:40:40","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1211"},"modified":"2024-02-06T15:58:02","modified_gmt":"2024-02-06T14:58:02","slug":"calculate-elapsed-time-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/calculate-elapsed-time-in-excel\/","title":{"rendered":"Calculate Elapsed Time in Excel"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><em><strong>What does it do? <\/strong><\/em><\/p>\n<p>Converts a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> to text and lets you specify the display formatting by using special format strings<\/p>\n<p><em><strong>Formula breakdown: <\/strong><\/em><\/p>\n<p>=TEXT(<span style=\"color: #0000ff;\">value1 &#8211; value2<\/span>, <span style=\"color: #ff6600;\">format text<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<p>=TEXT(<span style=\"color: #0000ff;\">formula<\/span>,<span style=\"color: #ff6600;\"> a text string enclosed in quotation marks<\/span>)<\/p>\n<hr \/>\n<p>When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel\u00b4s <span style=\"text-decoration: underline; color: #0000ff;\"><a style=\"color: #0000ff;\" title=\"Excel\u00b4s TEXT Function\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/excels-text-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">TEXT function<\/a><\/span><\/p>\n<p>Sometimes data gets dumped into Excel with the following date &amp; time format:<\/p>\n<p><em>24\/01\/2015\u00a0 19:48:00.<\/em><\/p>\n<p>Using the <strong>TEXT<\/strong> function and entering a special text string can give you the time elapsed in Days, Hours, Minutes and Hours &amp; Minutes.<\/p>\n<p>See below how easy this is to implement.<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/M0e2iD8vvdA?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Text-Elapsed-Time.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/Text-Elapsed-Time.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Text-Elapsed-Time.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Enter the following to get the elapsed time in days:<\/p>\n<p>We need to enter the <strong>TEXT<\/strong> function in a blank cell:<\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=TEXT(B12-A12, &#8220;dd&#8221;)<\/span><\/h2>\n<p>value1 is the <strong>end date time<\/strong><br \/>\nvalue2 is the <strong>start date time<\/strong><br \/>\nformat text is &#8220;<strong>dd<\/strong>&#8221; which signifies <strong>days<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9855\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed1.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"735\" height=\"385\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed1.png 735w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed1-300x157.png 300w\" sizes=\"(max-width: 735px) 100vw, 735px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong> <\/span>Apply the same formula to the rest of the cells by dragging the lower right corner downwards.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9856\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed2.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"753\" height=\"293\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed2.png 753w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed2-300x117.png 300w\" sizes=\"(max-width: 753px) 100vw, 753px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong> <\/span>Enter the following to get the elapsed time in hours:<\/p>\n<p>We need to enter the <strong>TEXT<\/strong> function in a blank cell:<\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=TEXT(B12-A12, &#8220;[hh]&#8221;)<\/span><\/h2>\n<p>value1 is the<strong> end date time<\/strong><br \/>\nvalue2 is the <strong>start date time<\/strong><br \/>\nformat text is &#8220;<strong>[hh]<\/strong>&#8221; which signifies <strong>hours<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9857\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed3.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"752\" height=\"278\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed3.png 752w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed3-300x111.png 300w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong> <\/span>Apply the same formula to the rest of the cells by dragging the lower right corner downwards.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9858\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed4.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"734\" height=\"303\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed4.png 734w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed4-300x124.png 300w\" sizes=\"(max-width: 734px) 100vw, 734px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> Enter the following to get the elapsed time in hours and minutes:<\/p>\n<p>We need to enter the <strong>TEXT<\/strong> function in a blank cell:<\/p>\n<h2 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=TEXT(B12-A12, &#8220;[h]:mm&#8221;)<\/span><\/h2>\n<p>value1 is the <strong>end date time<\/strong><br \/>\nvalue2 is the <strong>start date time<\/strong><br \/>\nformat text is &#8220;<strong>[h]:mm<\/strong>&#8221; which signifies <strong>hours and minutes<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9859\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed5.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"847\" height=\"277\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed5.png 847w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed5-300x98.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed5-768x251.png 768w\" sizes=\"(max-width: 847px) 100vw, 847px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong><\/span> Apply the same formula to the rest of the cells by dragging the lower right corner downwards. And your elapsed time results are all ready!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Calculate Elapsed Time in Excel\"  class=\"aligncenter size-full wp-image-9860\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed6.png\" alt=\"Calculate Elapsed Time in Excel\" width=\"761\" height=\"298\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed6.png 761w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/05\/elapsed6-300x117.png 300w\" sizes=\"(max-width: 761px) 100vw, 761px\" \/><\/p>\n<p><span style=\"color: #0000ff;\"><strong>How to Use the Text Formula in Excel<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; What does it do? Converts a formula to text and lets you specify the display formatting by using special format strings Formula breakdown: =TEXT(value1 &#8211; value2, format text) What it means: =TEXT(formula, a text string enclosed in quotation marks) When you have two points in time and you want to calculate the amount of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17412,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,14],"tags":[43,70],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1211"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1211"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1211\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17412"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}