Excel help!

Discussion in 'General Chat' started by spidermanalf, Jan 17, 2008.

  1. spidermanalf

    spidermanalf
    Active Member

    Joined:
    Oct 25, 2002
    Messages:
    2,000
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    63
    Location:
    Wakefield
    Ratings:
    +103
    OK, I have tried googling, and cannot find anything.

    I am trying to add up times, they are in teh format of

    hour:minutes:seconds.

    I am trying to add them all up, but cannot seem to do so.

    I know I may have to add hours: onto the ones with just minutes and seconds on.

    Can anyone help?

    Thanks in advance!
     

    Attached Files:

  2. Kristian

    Kristian
    Well-known Member

    Joined:
    Oct 1, 2002
    Messages:
    2,249
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    83
    Location:
    Sunny Scunny
    Ratings:
    +446
    Have you searched the Excel help for adding/summing times?
     
  3. Kristian

    Kristian
    Well-known Member

    Joined:
    Oct 1, 2002
    Messages:
    2,249
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    83
    Location:
    Sunny Scunny
    Ratings:
    +446
    You have leading spaces in each cell. Remove the spaces and use the usual SUM(cell1:cell2) formula.

    Kris.
     
  4. spidermanalf

    spidermanalf
    Active Member

    Joined:
    Oct 25, 2002
    Messages:
    2,000
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    63
    Location:
    Wakefield
    Ratings:
    +103
    Excellent thanks KrisLee, seems to work, now only to delete the first space, and add 0: to all the one without hours, on about 1500 lines of data, then repeat 15times.

    Least I will be busy tomorrow!
     
  5. Kristian

    Kristian
    Well-known Member

    Joined:
    Oct 1, 2002
    Messages:
    2,249
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    83
    Location:
    Sunny Scunny
    Ratings:
    +446
    No problem.

    To remove the leading spaces you could try selecting the cells/rows/columns and doing a find/replace. Put a space (or two) in the find, nothing in the replace, and select replace all.

    To add the leading 0's you could try concatenating ('&' I think) the '0' with the cell in question (e.g. in cell c1 put ='0' & A:1) into another column/row and then cut and paste into the original cell. This formula/rule could be then dragged/copied down to cover all the necessary cells. The help should tell you all about this.

    I'm not that good with Excel so there may be a better/easier method.

    Regards,

    Kris.
     

Share This Page

Loading...