Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Adding times together

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am having a hard time adding lengths of time together. For instance, I have 20 hours 5 mins and 23 sec that I want to add to 1 hour 36min and 58sec. When I try to SUM these two up to get the total amount of hours they keep giving me the time of day, not the hours totaled up. I have the information set up as 20:05:23 and in a time format. Any help on how I can add these? Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-04 13:16, Jana wrote:
    I am having a hard time adding lengths of time together. For instance, I have 20 hours 5 mins and 23 sec that I want to add to 1 hour 36min and 58sec. When I try to SUM these two up to get the total amount of hours they keep giving me the time of day, not the hours totaled up. I have the information set up as 20:05:23 and in a time format. Any help on how I can add these? Thanks
    Custom format the cell where you sum up times as, e.g.,

    [h]:mm

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Format your cell where the total is by using the custom format [h]:mm:ss

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You said you have the cells formated as time format, but there are two similar formats: one to sum as time of day, the other to sum total time. You've selected the first one. Try the other (it's down a couple lines), that should do it.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've used the time format that does not have the AM next to it (3rd from the bottom), yet I still keep getting AM times and even years on all of my times with hours in them. I've now tried the custom format given to me and it keeps defaulting to the time format that I have been using. Whenever I put hours, min & sec in I get AM times. When I just put min & sec in I'm fine. I can even add the times together even if they have the AM in them, but then it wont add the times that have no hours in the time. Thank you so much for your help with this. Please let me know if I have completely confused you. Jana

  6. #6
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jana: In the cell where you are summing your times, Click on Format-Cell. Choose the number tab and select Custom from the category box. When you do, a new input box labeled type will appear. In that box enter [h]:mm:ss. The brackets tell Excel to exceed one day and total the hours. This will allow Excel to display hours greater than 12 or 24.

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It still would not add the times that didn't have hours (only min and sec) in to the total, but I figured out that if I just put a 0 as the hour it works. Now I will be able to tell how much time we spend on the phone!Thanks for all of your help, Jana

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 14:36, Jana wrote:
    It still would not add the times that didn't have hours (only min and sec) in to the total, but I figured out that if I just put a 0 as the hour it works. Now I will be able to tell how much time we spend on the phone!Thanks for all of your help, Jana
    I'm having another problem now. When I add my times together some times it equals to more htan 24 hours. What happens is i'm left with the diffence between the 24hours and what is left over. For example, we were colectily on the phone for 29hoursm 53 min and 24sec, excel is only showing that I was on for 5:53:24. Any ideas on how I can get the real time to show? Thanks again for your help. Jana

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 10:37, Jana wrote:
    On 2002-04-04 14:36, Jana wrote:
    It still would not add the times that didn't have hours (only min and sec) in to the total, but I figured out that if I just put a 0 as the hour it works. Now I will be able to tell how much time we spend on the phone!Thanks for all of your help, Jana
    I'm having another problem now. When I add my times together some times it equals to more htan 24 hours. What happens is i'm left with the diffence between the 24hours and what is left over. For example, we were colectily on the phone for 29hoursm 53 min and 24sec, excel is only showing that I was on for 5:53:24. Any ideas on how I can get the real time to show? Thanks again for your help. Jana
    Excel will assume it's the next day i.e. 23hours + 4 hours = 3hours

    use the custom format offers several times (time he he! )
    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •