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: , , , , , , , , , , , ,
20 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.

  9. Carla says:

    I need to set conditional formatting to compare time entered in a cell against the TIME in cell A1. I need any cell in column H to turn orange if the time entered in Column H is within 1 hour prior of cell A1. Can you assist? And cell A1 is formatted in a 24-hour clock….. =TEXT(NOW(), “HH:mm”)+TIME(5,0,0)

  10. stephen roberts says:

    Hi Tom,

    Greetings from Scotland. I’m pulling my hair out trying to fix a gremlin in a spreadsheet for work. My Boss started constructing it to report on Standard hours worked, Overtime hours and Higher grade paid time. My issue is that the way it has been formatted, that it’s set up to show standard time as 35hours with any additional standard hours automatically populating a separate overtime column. All fine so far, however for some reason it always shows 35 standard hours (even when no daily hours are entered, which is a problem when that then adds into a total hours column). It wasn’t an issue until I tried to add in nightshift higher grade hours, as its now adding unworked daytime hours (the 35) to my total.

    I’d be extremely grateful if you would be willing to assist me.

    • Tom Urtis says:

      Using my picture as an example, you can see that neither regular nor overtime hours would be represented when there is a zero or no hours worked in column B, although in this picture all the people have hours worked but if any did not, no hours or pay would show.

      The way I have my worksheet set up, which is probably different than yours but the concept is still the same, I just changed the number in cell B3 from 40 to 35 and everything calculates properly, with no “35” popping up for people who did not record any worked hours.

  11. Hamza says:

    Hi,
    Need one help,

    I have planned working hours which is 324:00:00 (HH:MM:SS) and achieved hours 279:25:00 (HH:MM:SS), when i am using simple subtraction it returns nothing. Kindly help me out.

    Thanks

    • Tom Urtis says:

      Just now I entered this into cell A1
      324:00:00
      and this in cell A2
      279:25:00

      In cell A3 I have this “simple subtraction” as you call it:
      =A1-A2
      which returns
      44:35:00
      which looks correct to me.

      So not only do I do what you said you do,
      I got a returned value from my formula
      and
      the returned value is correct.

      So unless you tell me more than what you told me so far, I cannot duplicate your problem.

  12. Usman Javed says:

    HI Need one Help please

    First_Enquiry_Date Resolved Date Total Time Solution Required After Excluding
    11/03/2023 20:14 12/03/2023 16:19 20:04 I want to Exclude Time From night 10 PM to Morning 08 AM 10:04

Leave a Reply

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

*