Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Formula examples for hours, minutes, and seconds being added or subtracted from time.

Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)

Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)

Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)

Combination of Hours, Minutes, and Seconds
Example to add 3 hours, 16 minutes, and 48 seconds: =$B$1+TIME(3,16,48)
Example to subtract 3 hours, 16 minutes, and 48 seconds: =$B$1-TIME(3,16,48)

When you first enter your formulas, the range will look odd, similar to this:

To fix that, select the range, press Alt+O+E and format the range as Time.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
12 comments on “Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds
  1. Marcel says:

    Thank you so much!

    This was really helpful, wouldn’t have been able to figure this out myself
    and most other sites are not mentioning ‘Time’.

    Again, many thanks!

  2. Gary Thomson says:

    If the start time is for example 3AM and I want to subtract 5 hours the answer should be 10PM, but the formula =$B$3-TIME(5,0,0)doesn’t work, cell shows #######.

    Any way to get this to work?

    • Tom Urtis says:

      You’re right, I didn’t cover the midnight thing in that example when subtracting time.

      Suppose as you say, you have 3:00 AM in cell B3.
      You want to subtract 5 hours from that, to return 10:00 PM.
      In some other cell, enter the formula
      =MOD(B3-“5:00”,1)

      I’m updating my website and will add the “midnight factor” in future formula examples.

  3. Dave King says:

    How would I subtract a time total (29:41 mm/ss) generated by a formula from a total time of (45:00 mm/ss)?

    Thanks!

    • Tom Urtis says:

      Times are entered as hours, minutes, and seconds. In your question, there are no hours involved, but to get the answer, you’d want to enter the times in a cell whereby Excel is comfortable working with the times and then format the result to make sense when you see it visually.

      Suppose you want to enter the 45 minutes total time in a cell, let’s say cell A1. You can do that by entering it like this:
      0:45:00
      The preceding 0 (using the colon character to separate it from the 45) serves as the placeholder for hours. If you had entered 45:00 then Excel would regard that as 45 hours and zero minutes. But to Excel, 0:45:00 means zero hours, 45 minutes, and zero seconds, which is what you want.

      With 0:45:00 in cell A1, the same logic applies for entry of 29:41 into cell A2, meaning you’d enter 0:29:41 into cell A2.

      In cell A3, enter the formula =A1-A2.
      If in cell A3 you do not see the result of 0:15:19, then format the cell as [h]:mm:ss.
      If you don’t want to see the preceding zero to represent no hours, then format cell A3 (or all 3 cells if you want in this case) as [mm]:ss and you will see the visual result of 15:19.

  4. Doug Johnson says:

    I need to find the difference between times over two consecutive days; e.g.

    Day one: 23:02:10
    Day two: 00:03:25

    i.e. subtract Day one from Day two (the day info is not required)

    • Tom Urtis says:

      Covering a few scenarios depending on how your data is arranged…

      For times that cross midnight, example 17:00 to 02:00 then use the formula
      =A2-A1+(A1>A2)

      or
      =C9+(C8>C9)-C8

      or
      =MOD(C9-C8,1)

      or
      =MOD(B1 – A1, 1)*1440
      The 1440 represents minutes in a day.

      Suppose you have 3:00 AM in cell B3. You want to subtract 5 hours from that, to return 10:00 PM.
      In some other cell, enter the formula
      =MOD(B3-“5:00”,1)
      format result cell as [h]:mm

  5. Jeff Sanders says:

    Thanks, Very helpful

  6. Tony says:

    I’m entering a data which in the format hrs.min (874.52 880.40), which are machine hours, how can we subtract them…

  7. Crispo Mwangi says:

    Interesting & Helpful.

    Thanks.

  8. Arlene says:

    I have done this but I am getting a #value

    • Tom Urtis says:

      I know the formulas work, so assuming you followed the instructions as I wrote them, it could be a case of international syntax difference(s) or maybe the original data (the times in the cells you are calculating) are not really times but text. Please check your cells and formulas again, and if you still get an error, post back with
      • what value you have in what cell
      • the formula you are using
      • how you formatted the cells
      • what your expected result is

      Something is happening on your worksheet that is different than what my pictures and descriptions are showing.

Leave a Reply

Your email address will not be published. Required fields are marked *

*