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

Thread: Have one more Time issue

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey All,

    Here is my next time issue. I am trying to add a list on hh:mm:ss in a column to get a total time. I have my total cell formatted for [h]:mm:ss and the formula of "=TIMEVALUE(O2:O38)" and I am getting #VALUE in the cell where the total is displayed. If I just try to add these cells in the column I get "00:00:00". However if I go in and type "=(O2+O3+O4+etc...)" they start adding the time in the cell. Can you help me out please?


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select your "list of hh:mm:ss" values, choose the Data | Text to Columns... wizard, and press [ Finish ].

    Now, you can total your times using...

    =SUM(O2:O38)

    [ This Message was edited by: Mark W. on 2002-05-13 09:26 ]

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks... however that did not work. Still getting "00:00:00". Any other suggestions?


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default



    try =Sum(O2:O38)


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya Trouble,

    Feel free to kick me an email and I'll take a look at the file. - you may want to tweak the file or even make a test file if you've got any confidential info.

    asala42@yahoo.com

    Adam

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Maybe this will help,

    I have 2 columns that I figured out the difference in time:
    "J2 & K2"
    "2/25/02 13:00:07 & 2/25/02 13:35:46"

    I have that going to "O2" thru "O38" with the following:
    "=INT(SUM(K2-J2)/0.04166667)&":" &MINUTE(MOD(SUM(K2-J2),1))&":" &SECOND(MOD(SUM(J2-K2),1))"

    Now I am trying to get a "total time" from that columns "O2 thru O38" by using:
    "=SUM(O2:O38)"
    I place this formula in the "O40" cell with the cell formatted to:
    "[h]:mm:ss"
    Getting the result of:
    "0:00:00"

    If I place this into "O40":
    "=SUM(O2+O3+O4 etc..."
    It WORKS!! And will add the columns and give me a total.

    I have a slight problem however... This is going to total 50,000 lines and Excel will not let me (thank God) type all of those cells in.

    I tried to do the "Data/Column to text" method as stated above with the =SUM(O2:O38) with no effect.

    Still looking for a solution... Maybe the full story helps with the problem.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh I think I got it,

    Try:

    =Sumproduct((O2:O38)+0)

    Adam

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That did it!!! YES!!!

    Thanks a ton...

    Dan

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 10:25, TroubleMaker wrote:
    Thanks... however that did not work. Still getting "00:00:00". Any other suggestions?

    The following works fine...

    1. Select your "list of hh:mm:ss" values
    2. Choose Data | Text to Columns...
    3. Press [ Finish ].

    Now, you can total your times using...

    =SUM(O2:O38)


    Try it again!

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
  •