{"id":3681,"date":"2021-10-19T20:48:46","date_gmt":"2021-10-20T03:48:46","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3681"},"modified":"2021-10-19T20:48:47","modified_gmt":"2021-10-20T03:48:47","slug":"add-or-subtract-time-in-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3681","title":{"rendered":"Add or Subtract Time in Excel"},"content":{"rendered":"\n

Add or subtract time<\/h1>\n\n\n\n

Let’s say that you need to add two different time values together to get a total. Or, you need to subtract a one-time value from another to get the total time spent working on a project.<\/p>\n\n\n\n

As you’ll see in the sections below, Excel makes it easy to add or subtract time.<\/p>\n\n\n\n

Add time<\/h3>\n\n\n\n

Suppose that you want to know how many hours and minutes it will take to complete two tasks. You estimate that the first task will take 6 hours and 45 minutes and the second task will take 9 hours and 30 minutes.<\/p>\n\n\n\n

Here is one way to set this up in the a worksheet.<\/p>\n\n\n\n

  1. Enter 6:45 in cell B2<\/strong>, and enter 9:30 in cell B3<\/strong>.<\/li>
  2. In cell B4, enter =B2+B3<\/strong> and then press Enter.\"TimeThe result is 16:15\u201416 hours and 15 minutes\u2014for the completion the two tasks.Tip: <\/strong>You can also add up times by using the AutoSum<\/strong> function to sum numbers. Select cell B4, and then on the Home<\/strong> tab, choose AutoSum<\/strong>. The formula will look like this: =SUM(B2:B3).<\/strong> Press Enter to get the same result, 16 hours and 15 minutes.<\/li><\/ol>\n\n\n\n

    Well, that was easy enough, but there\u2019s an extra step if your hours add up to more than 24. You need to apply a special format to the formula result.<\/p>\n\n\n\n

    To add up more than 24 hours:<\/strong><\/p>\n\n\n\n

    1. In cell B2 type 12:45<\/strong>, and in cell B3 type 15:30<\/strong>.<\/li>
    2. Type =B2+B3<\/strong> in cell B4, and then press Enter.\"TimeThe result is 4:15, which is not what you might expect. This is because the time for Task 2 is in 24-hour time. 15:30 is the same as 3:30.<\/li>
    3. To display the time as more than 24 hours, select cell B4.<\/li>
    4. On the Home<\/strong> tab, in the Cells<\/strong> group, choose Format<\/strong>, and then choose Format Cells<\/strong>.\"On<\/li>
    5. In the Format Cells<\/strong> box, choose Custom<\/strong> in the Category<\/strong> list.<\/li>
    6. In the Type<\/strong> box, at the top of the list of formats, type [h]:mm;@ <\/strong>and then choose OK<\/strong>.

      Take note of the colon after [h]<\/strong> and a semicolon after mm<\/strong>.<\/li><\/ol>\n\n\n\n

      The result is 28 hours and 15 minutes. The format will be in the Type<\/strong> list the next time you need it.<\/p>\n\n\n\n

      Subtract time<\/h3>\n\n\n\n

      Here’s another example: Let’s say that you and your friends know both your start and end times at a volunteer project, and want to know how much time you spent in total.<\/p>\n\n\n\n

      Follow these steps to get the elapsed time\u2014which is the difference between two times.<\/p>\n\n\n\n

      1. In cell B2, enter the start time and include \u201ca<\/strong>\u201d for AM or \u201cp<\/strong>\u201d for PM. Then press Enter.<\/li>
      2. In cell C2, enter the end time, including \u201ca<\/strong>\u201d or \u201cp<\/strong>\u201d as appropriate, and then press Enter.<\/li>
      3. Type the other start and end times for your friends, Joy and Leslie.<\/li>
      4. In cell D2, subtract the end time from the start time by entering the formula =C2-B2<\/strong>, and then press Enter.\"One<\/li>
      5. In the Format Cells<\/strong> box, click Custom<\/strong> in the Category<\/strong> list.<\/li>
      6. In the Type<\/strong> list, click h:mm<\/strong> (for hours and minutes), and then click OK<\/strong>.Now we see that Richard worked 3 hours and 45 minutes.\"Elpased<\/li>
      7. To get the results for Joy and Leslie, copy the formula by selecting cell D2 and dragging to cell D4.The formatting in cell D2 is copied along with the formula.<\/li><\/ol>\n\n\n\n

        To subtract time that\u2019s more than 24 hours:<\/strong><\/p>\n\n\n\n

        It is necessary to create a formula to subtract the difference between two times that total more than 24 hours.<\/p>\n\n\n\n

        Follow the steps below:<\/p>\n\n\n\n

        1. Referring to the above example, select cell B1 and drag to cell B2 so that you can apply the format to both cells at the same time.<\/li>
        2. In the Format Cells<\/strong> box, click Custom<\/strong> in the Category<\/strong> list.<\/li>
        3. In the Type<\/strong> box, at the top of the list of formats, type m\/d\/yyyy h:mm AM\/PM<\/strong>.Notice the empty space at the end of yyyy<\/strong> and at the end of mm<\/strong>.The new format will be available when you need it in the Type<\/strong> list.<\/li>
        4. In cell B1, type the start date, including month\/day\/year and time using either \u201ca<\/strong>\u201d or \u201cp<\/strong>\u201d for AM and PM.

          \"Elapsed<\/li>
        5. In cell B2, do the same for the end date.<\/li>
        6. In cell B3, type the formula =(B2-B1)*24<\/strong>.<\/li><\/ol>\n\n\n\n

          The result is 31.5 hours.<\/p>\n","protected":false},"excerpt":{"rendered":"

          Add or subtract time Let’s say that you need to add two different time values together to get a total. Or, you need to subtract a one-time value from another to get the total time spent working on a project. As you’ll see in the sections below, Excel makes it easy to add or subtract time. Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[913,912],"class_list":["post-3681","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-add-or-subtract-time","tag-add-or-subtract-time-in-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3681","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3681"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3681\/revisions"}],"predecessor-version":[{"id":3683,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3681\/revisions\/3683"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3681"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3681"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3681"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}