Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Time "2.17 (2min & 17 sec) to 2 17/60 min) how

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

    Default

    I have a col of time in the format 2.17 that is 2 min and 17 sec. How this can be convert to 2 17/60 min.
    Thank you

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    With your number in A1, you could use

    =INT(A1)+(A1-INT(A1))/0.6

    and apply a custom format of
    # ??/60

    good luck

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 09:43, kaneson wrote:
    I have a col of time in the format 2.17 that is 2 min and 17 sec. How this can be convert to 2 17/60 min.
    Thank you
    use the formula

    =SUBSTITUTE(REPLACE(A1,LEN(A1)+1,3,"/60"),"."," ")+0

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi IML:
    Yours is alot neater ... beautiful!

    regards

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. I started down the same road you did, but distingushing between 3.05 and 3.5 got a little messy. I guess something along the lines of
    =SUBSTITUTE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))+1,3,"/60"),"."," ")+0

    would cover that.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 10:17, IML wrote:
    Thanks. I started down the same road you did, but distingushing between 3.05 and 3.5 got a little messy. I guess something along the lines of
    =SUBSTITUTE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))+1,3,"/60"),"."," ")+0

    would cover that.
    Hi IML:
    If we are willing to use the Substitute|Replace formula, mine would work fine without having to make the modification you suggested; e.g.

    I tried 3.05 ... and I got 3 with 3 5/60
    now recall, we don't have to force 3 05/60

    and then with 3.50 ... I got 3 50/60

    I did not try your suggested modification -- when mine worked the way I had intended it to work, I stopped there.

    Regards!

    [ This Message was edited by: Yogi Anand on 2002-05-10 10:27 ]

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

    Default

    Thank you, it work!!!

    I work this way.

    =TRUNC(E2)+(E2-TRUNC(E2))*100/60

    Is there any other options other than this two

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 10:53, kaneson wrote:
    Thank you, it work!!!

    I work this way.

    =TRUNC(E2)+(E2-TRUNC(E2))*100/60

    Is there any other options other than this two
    Hi Kaneson:
    Here we go:

    =INT(E2)+MOD(E2,1)/60

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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
  •