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. The second example explains how to sum times when the total is greater than 24 hours.

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

  4. 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. 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. Many thanks, this did answer my problem of adding working hours.

  7. My pleasure!.

  8. 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?

    • 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.

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

  10. 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

    • 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.

  11. 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!?

    • 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.

      • 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.

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

  12. 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?

  13. 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?

    • 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.

  14. 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.