How to add or sum times

If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.

Example 1 : total hours less than 24.

Cells B1, B2, B3 and B4 contains the times.

Cell B5 (total) contains the formula:

=SUM(B1:B4)

The result of this sum is 23:05 (23 hours and 5 minutes).

Please note that cells B1to B5 are formatted as time (hh:mm).

addtime1

Example 2 – total hours greater than 24.

Cells B1, B2, B3 and B4 contains the times.

The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).

To avoid this problem we use the following formula instead: =SUM(B1:B4)*24

We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).

addtime2

Applies to Excel 2003

Comments

  1. Trevor Smith says:

    What do you do if you have

    B1 21:36:41
    B2 2:51:46
    B3 3:20:00
    Total
    B4 3:48:27

    When I should be around 26 Hours total

  2. admin says:

    The second example explains how to sum times when the total is greater than 24 hours.

  3. DeDaMrAz says:

    What to do if B1 value is greater than 24h, if it is say 121:24:14 (hh:mm:ss)

  4. admin says:

    Format the cell with this custom format :

    [h]:mm:ss

    See this post for more details:

    http://www.exceldigest.com/myblog/2009/11/29/how-to-some-times-greater-than-24-hours-in-excel-2007/

  5. DeDaMrAz says:

    In the end it does work, but after formatting all the cells like that I had to double-click on each and every one :-( and after that I ended up with some cells with values like this 1/5/1900 05:25:31 instead of 125:25:31…

    Imagine 1000+ entries that you have to double-click, and\or change back… very time consuming…

    I will stick to plain and simple formulas like =A1+A2+A3… :-) for now

    Thank you never the less.

  6. admin says:
  7. Brian says:

    Many thanks, this did answer my problem of adding working hours.

  8. admin says:

    My pleasure!.

  9. Jason says:

    Hoping someone can help here, What I am trying to do is to add a time amount to a base time. Basically, I’m trying to take a departure time, add travel time, and get the arrival time. I entered in my departure time cell “11:00″ for 11 AM, and in the travel time cell “2.87″ for 2 hours and 49 minutes. I would like the sheet to add travel time to the departure time and the result be “1:49 PM” in the arrival time cell. Is this possible?

    • admin says:

      It easier to insert the travel time as HH:MM e.g. 2:15 i.e. 2 hours and 15 minutes. Then you can add the 2 times easily. However if you want to use it as decimal value then you can use the following formula:

      =A1+TIME(INT(B1);(B1-INT(B1))*60;0)
      Where A1 has the departure time. And B1 has the travel time.

  10. Aaron says:

    Use [h] instead of h in the formula to tell Excel not to zero-out 24-hour time periods.

  11. Roberta says:

    I need to know what is the difference between h:mm:ss and [h]:mm:ss, and why if Ill try to add to formats of

    h:mm:ss + h:mm:ss the result has to be in [h]:mm:ss

    please help me

    • admin says:

      Suppose you are adding two times: 15:30:00 + 11:15:00
      If your format of the result cell is h:mm:ss , the result will be 02:45:00. Any thing more than 24 hours will be dropped.
      But if your format of the result cell is [h]:mm:ss, then the result will be 26:45:00.

  12. JEP says:

    I am trying to calculate a time sheet so I can keep tract of my work hours. I can’t get the total for the end of the week it keeps multiplying. Here is the formula I am using for one day:

    =(D3-C3+IF(C3>D3,1))-Formulas!B2 I have the Time 00:00 format with this formula

    D3 is my time out, C3 is my time in, Formulas!B2 is my lunch hour (0:30), (my normal hours are 8:00 AM to 4:30 PM, lunch included, so I work 8 hours). For the D3 and C3 cells I have the Text formate on and I enter time like 8:00 AM in these cells.

    When I put in the =SUM() formula to add my hours from each day of the week together it multipies my hours.
    For example:

    In E3 and E4 it has 8:00 in each row (calculated by above formula). In my Week Total row I have the formula =SUM(E3,E4,E5,E6,E7,E8,E9) with the Custom [hh]:mm (I removed the :ss part) format. But instead of being 16:00, it totals to 64:00.

    Could you please help me!?

    • admin says:

      All time cells must be formatted as time in order for the totals to work properly. You are formatting some cells as text. This could be your problem.

      • JEP says:

        Thanks, that solved the problem. Now I have another problem. I want to enter a “0″ into the areas where I didn’t work, but when I do its says “12:00 AM”. I was trying to set up an IF function on the second worksheet so I if I type in the cell it won’t delete my IF function formula (or other function or formula I may need). I want it to return a “0″ instead of “12:00 AM” and when I enter a time I would like it to display the time I entered. Can you help me with this problem too? I would really appreciate it!!

        Again I am using Excel 2007.

      • admin says:

        Format the cell as hh:mm without “AM/PM”

  13. Ben says:

    Suppose I have an in and out time

    Column 1: In
    Column 2: Out
    both are formated as XX:XX am/pm

    What function can I use to got the total hours worked that day?

  14. Sam says:

    I’m adding up time segments and get a total of 96hrs, no problems. Then I need to convert that to decimal to use elsewhere…,I was using: =HOUR(U27)+(MINUTE(U27)/60) and that seems to work on everything under 24hrs, but for 96hrs it changes it to 23.93, (formatted as a number to 2 decimal places). Can you help?

    • admin says:

      The Hour function works only for hours less than 24.
      Try this :
      = U27*24
      I tested with a number of values and it looks fine. I hope it is consistent all through.

  15. JEP says:

    Can I send you my worksheet to look at with a list of questions? It would be so much easier. I have several more questions and the calculations are not quite correct.