Results 1 to 6 of 6

Thread: Time Formats

  1. #1
    Scott
    Guest

    Time Formats

    I want to be able to record and sum elapsed times that activities take. But whenever I enter a time, Excel assumes my entry is a certain hour of the day or year. For example, if I enter 8:45, 9:00, and 9:15 in three cells and sum them, I'd like to get a result of 27:00. I've tried the various time formats and custom formats, but so far have not been successful. I'm using Excel 2000 (9.0.3821 SR-1). Any ideas?

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Formats

    It's messy, but splitting the number of hours into int and fraction parts and then putting them back together works:

    If your numbers are in c3:c5 then the formula is
    =INT(24*SUM(C3:C5)) & ":" & TEXT( (24*SUM(C3:C5)-INT(24*SUM(C3:C5)))*60,"00")

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Formats

    Better still there's a number format that does it:

    [hh]:mm
    (including the square brackets)

    Ian.

  4. #4
    Scott
    Guest

    Re: Time Formats

    Thank you Ian. Your suggestion is VERY helpful!

    Do you, or does anyone else, know if something similar could be done if the times being summed involved seconds, and/or fractions of seconds (as you might use in track and field for instance)?

    THANKS!

    Scott

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Formats

    Try [hh]:mm:ss.00

    Tony

  6. #6
    Scott
    Guest

    Re: Time Formats

    Thank you folks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> --Scott

Posting Permissions

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