Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting mins (2003)

    Is it possible to create a custom number format so that times entered as 0:01, 0:02 (representing minutes) can be displayed as '1 min', '2 min'. 59 min should be followed by 0 min. Any ideas? Thanks, Andy.

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

    Re: Counting mins (2003)

    Not as far as I know, for the format code m for minutes is also used for months. According to the online help, m is only interpreted as minutes if it is preceded by h: (for hours) and/or followed by :ss (for seconds), in all other cases it is interpreted as months:
    <hr>The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, Microsoft Excel displays the month instead of minutes.<hr>
    You could use formulas in another column however (and hide the time column if desired). With a time such as 0:13 in cell A2, the formula
    <code>
    =MINUTE(A2)&" min"
    </code>
    will display "13 min". You can fill down this formula if needed. Alternatively, you can use
    <code>
    =MINUTE(A2)
    </code>
    and format the cell as <code>0 "min"</code>.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting mins (2003)

    <P ID="edit" class=small>(Edited by JohnBF on 19-Apr-07 08:45. )</P>Hans solution is more elegant, and works with the way you want to enter minutes, but FWIW I usually enter minutes as =minutes/1440 (1,440 minutes in a day) cell formatted as [h]:mm. If you preformat the cell as [h]:mm:ss (the seconds are optional), you can also enter minutes by keying in a zero (or other number) for hours, then a colon separator, then the minutes, such as "0:13". If you enter 0:133 (133 minutes), Excel accepts it and converts the display to 2:13 (2 hours 13 minutes).
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting mins (2003)

    Thank you both for your time - see what I did there? Andy.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting mins (2003)

    How about a custom format of

    [m] "min"

    Type in 0:59 in the cell and it comes out as 59 min just typing in 0 get 0 min.

    The format of [m] is used to calculate the minutes after midnight and "tricks" excel into formatting as minutes

    Hope that helps
    Jerry

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

    Re: Counting mins (2003)

    The idea of using [m] instead of m is clever, but Andy asked
    <hr>59 min should be followed by 0 min.<hr>
    . With the format you suggest, 59 min will be followed by 60 min.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting mins (2003)

    oops, I see the question now, well the only way to dig myself out of that hole is to use an if statement using ideas similar to John's above

    =IF(A1>59,0,A1/1440)

    Not perfect but could be used but I haven't seen the model to see if it has an affect
    Jerry

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

    Re: Counting mins (2003)

    My interpretation of the question was that Andy wanted

    ...
    0:58 > 58
    0:59 > 59
    1:00 > 0
    1:01 > 1
    ...

    But I could be wrong, of course.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting mins (2003)

    OK

    I understand your thinking then shall we just use a mixture of your original and mine to get

    =MINUTE(A2)/1440

    and my idea of the formatting to provide [m] "min"

    It seems to do the trick in the attached excel spreadsheet..what do you think?
    Jerry

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

    Re: Counting mins (2003)

    Yes, that will result in the values Andy wants, as i interpret it.

    (But I still think my second suggestion is simpler <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting mins (2003)

    I agree. Though I think the techniques I describe are the "right" way to display times, I'm constantly explaining, even to fellow accountants, how I'm entering times as decimal parts of a day to mutliply by FTE hourly wage rates. (Are your eyes glazing over? Theirs do.)

    It's easier to just leave the minutes as straight decimal numbers, and bury the time conversions in the product formulas. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting mins (2003)

    A lot of good ideas here. Thank you. Perhaps it might be simpler to create a custom list from '0 min' to '59 min'? So long as the actual minute value won't be required in a formula this would display correctly. Andy.

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

    Re: Counting mins (2003)

    If it's for your own use, that's fine. If you want others to use this, you'd have to instruct them to create their own custom list. (Custom lists are user-level settings, they don't travel with the workbook.)

Posting Permissions

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