Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2010
    Location
    Exton, PA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Greetings everyone!

    I have a spread sheet where I am tracking phone stats for a help desk organization. Specifically, I am tracking the average time to answer and the average duration time of calls. I am entering time in minutes and seconds, but Excel seems to have issues with formatting the numbers correctly.

    I have done some research, and have found these two cell formats.

    [h]:mm:ss

    and

    h:mm:ss

    First of all, what's supposed to be the difference between those two?

    And secondly, I've been using the first one and it appears that if I want to enter "1 minute, 25 seconds", I have to type it out as "0:01:25". If I just type "1:25", the formatting changes it to "1:25:00", or 1 hour, 25 minutes.

    Is there another (easier?) way to either format the cell or enter the information so that Excel understands what I'm talking about?

    ...or have I already found the best way?

    Thank you!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ed,

    I did a little poking around and it looks like you've found the best way already.
    I tried skipping the leading 0 for hours and it displayed ok but when I tried to =Sum()
    a column of numbers they wouldn't add up.

    Depending on the volume of numbers you have to enter you could set up a form and some VBA code to streamline the operation where you'd have separate input areas for hours, minutes, seconds and just use the tab key to move between them then have the VBA code assemble it for entry into the cell. You could even have the form default to the minutes field because I doubt if you have many entries where Hours are a factor.

    The form would also be good for error checking because if you enter 0:59:80 into a time field formatted [h]:mm:ss it will convert to 1:00:20.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Apr 2010
    Location
    Exton, PA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    VBA huh?

    A very interesting idea. I don't have much VBA experience but it will give me something to look up and study.

    Thanks RG! I'll give it a try.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You could enter the H,M,S into 3 seperate columns and then compute the duration using the time function.
    This will then give the actual duration

    The Formula Syntax is =TIME(H,M.S)

    IN ADDITION, the time function would support 73 min and correctly convert to 1 Hour 13 Min

    The difference between the formats is

    h:mm:ss Shows time of Day and if H exceeds 24 it will become 1 Day

    [h]:mm:ss Shows time duratations so as long as you use this format throughout you will get total time in Hours Min Secs

    [attachment=88508:EnterTimes.jpg]

    See attached spreadsheet and image for an example using 3 columns to enter Data (If needed you could also hide the computed column).

    I have used SUMIF and COUNTIF to calculate Average against the Computed Time Columns because
    Average would consider the 0's to be significant and give a false average.

    IF you are using Excel 2007 you have an AVERAGEIF Function available to you

    [attachment=88509:EnterTimes.xls]
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

Posting Permissions

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