Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Summarize Time (97)

    Good afternoon,

    Can anyone explain to me why I can't use the Sum button (Sigma) to sum up timecells? And more important: how can I sum up timecells?

    Many thanks in advance!

    Daphne

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Summarize Time (97)

    The main problem with summing time values is that the default time format can't handle values greater than 24 hours. There is a built-in format for values above 24 hours; it is listed (in my version of Excel) as "37:30:55".
    You can also create custom formats; to allow time values above 24 hours, put square brackets around the hour symbol: for example [h]:mm or [hh]:mm:ss in the English version (it would be <!t>[u] or [uu] in the Dutch version).
    Apart from this, the Sigma button works OK for me - it sums time values correctly, but I have to set the time format as described above.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Summarize Time (97)

    Just like other cells. But the time will have to be recognized as time by excel. If they are in fact recognized as text, XL will not add them up.

    See the attached example file. The total time is shown in cell A8. Note the number format of that cell!
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Summarize Time (97)

    I disagree with your statment that the "default time format can't handle values greater than 24 hours". It handles them fine. When you speak of a time (without listing the day) 2:00 PM is 2:00 PM whether the day is Monday, January 1 or Wed Jan 3 or 14:35:15 on Monday is still 14:35:15 on Tuesday UNLESS you choose to include day information. You only are requesting it to DISPLAY the time of day (without reference to the day!) Maybe they want ELAPSED time "[h]:mm" not time of day "hh:mm" as you point out.

    I would not say that the "mm:ss" format can NOT handle time over 60 min, it handles it fine, it just DISPLAYS only the minutes and seconds (as requested). It might NOT be the format desired, but it handles it fine.

    The format "dddd" only gives 7 possible responses (the day of the week), but it handles the months, years, and hours an min okay, it is just the wrong format if you are looking for the time, year, month, etc.

    In ALL of the cases the VALUE could be the same, it is NOT a matter of what the format can handle, it is a question if the user has CHOSEN the correct format to DISPLAY his/her needs and if there is, in excel, the format desired.

    I see a lot of question about date/time because people don't completely understand, the difference between VALUES and DISPLAYS with formatting and how dates/times are actually stored in excel.

    Just some clarification
    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Summarize Time (97)

    Hi Steve,

    You're right, of course. In order to keep my answer short, I simplified it - perhaps oversimplified. I agree with you that it's important to be aware of the difference between stored and displayed values.

Posting Permissions

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