Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding time in hh:mm:ss

    [img]/w3timages/icons/hairy.gif[/img] I am adding a column of time in format hh:mm:ss, but in order for excel to add it properly, the formula must be + each cell. Range useage doesn't seem to work. Obviously, this is very time consuming. Anyone know how to make this process easier?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Karin,

    Is what is happening that when you sum a range, you don't get the right answer? For instance, I have 3 cells in a column with 10:00:00 in eac cell; when I sum them, the answer is 6:00:00. But thats because it's actually a date formatted as time- 30 hours past midnight is 6AM the next day.

    If that IS the case, format your sum cell with cust formatting as "[h]:mm:ss" and you will get the answer "30:00:00"
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    I either get an incorrect answer or 00:00:00. I changed the format of the sum cell to the custom format, as well as the data (tried time format for data as well). Still isn't working. If the formula reads =g76+g75 it works, tho.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Karin,

    Can you attach a bit of the spreadsheet?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Here's my sample.
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Karin,

    You appear to have text in the "time" fields.

    Highlight the column, and replace all "00:" with "0:".

    Does that give the right answer?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Excellent! That works great, until I use data that has hours: 08:27:15. Then it doesn't work. See sample
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time in hh:mm:ss

    Karin

    You can add a new formula in the column alongside:

    Either- if you haven't converted any times to text by the "replace" which you did:
    =TIMEVALUE(C7)

    Or, if you have done the replace (therfore you've got mixed time and text):
    =IF(ISNUMBER(C7),C7,TIMEVALUE(C7))

    copy the formula down the column, and formt that column; you can then Copy, Paste Special, Values, to put the tiimes back in their original locations.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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