+ Reply to Thread
Results 1 to 10 of 10

Ratio keeps converting to decimal

  1. #1
    Registered User
    Join Date
    12-24-2018
    Location
    London, England
    MS-Off Ver
    Office 360
    Posts
    10

    Ratio keeps converting to decimal

    Hello

    I'm trying to insert 20:60 into a cell but the cell keeps converting the 20:60 into the number 0.875.
    I dont want that. I just want the cell to say 20:60

    How can I fix that please?


    I've got other cells that say 20:40 , and 20:20 and they look just fine

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    2016
    Posts
    498

    Re: Ratio keeps converting to decimal

    Right Click > Format Cells > Custom > Write HH:MM under Type:
    FYI, 20:60 = 21:00, so the cell will show 21:00

  3. #3
    Valued Forum Contributor
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    518

    Re: Ratio keeps converting to decimal

    format the cell as 'Text'

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,173

    Re: Ratio keeps converting to decimal

    Quote Originally Posted by Shareez Saleem View Post
    Right Click > Format Cells > Custom > Write HH:MM under Type:
    FYI, 20:60 = 21:00, so the cell will show 21:00
    I think OP is trying to show ratios, not time.

    Herotrader, the reason they look OK is that they are actually being shown as time, not a ratio. In excel, time is just a fraction of a day (24 hrs), 6:00 is actually If you think of this 0.25, 18: is 0.75, so based on what you show...
    20:60 is actually 21:00 (20:00 hrs plus 60 minutes) = 0.875
    20:40 is 40 mins past 8 PM
    20:20 is 20 mins past 8 PM

    Make sense?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-24-2018
    Location
    London, England
    MS-Off Ver
    Office 360
    Posts
    10
    Quote Originally Posted by Shareez Saleem View Post
    Right Click > Format Cells > Custom > Write HH:MM under Type:
    FYI, 20:60 = 21:00, so the cell will show 21:00
    Hello, I'm trying to show 20:60 ratio. Not time.
    Thanks

  6. #6
    Registered User
    Join Date
    12-24-2018
    Location
    London, England
    MS-Off Ver
    Office 360
    Posts
    10
    Quote Originally Posted by FDibbins View Post
    I think OP is trying to show ratios, not time.

    Herotrader, the reason they look OK is that they are actually being shown as time, not a ratio. In excel, time is just a fraction of a day (24 hrs), 6:00 is actually If you think of this 0.25, 18: is 0.75, so based on what you show...
    20:60 is actually 21:00 (20:00 hrs plus 60 minutes) = 0.875
    20:40 is 40 mins past 8 PM
    20:20 is 20 mins past 8 PM

    Make sense?
    Yes I'm trying to show a RATIO. How do I do that?

  7. #7
    Registered User
    Join Date
    12-24-2018
    Location
    London, England
    MS-Off Ver
    Office 360
    Posts
    10

    Re: Ratio keeps converting to decimal

    Hello

    I've fixed the problem in a roundabout way.

    I've put letters in the same cell. I suppose this confuses Excel and it keeps the 20:60 format

    I've written:

    RR 20:60

  8. #8
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,042

    Re: Ratio keeps converting to decimal

    You could also precede the string with an apostrophe (')
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  9. #9
    Valued Forum Contributor
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    518

    Re: Ratio keeps converting to decimal

    You could of course formatted the cell as 'Text'.
    If you then need to do 'maths' on the cell, you have a rather convenient separator ' : '
    Rather than adding inbuilt problems.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,173

    Re: Ratio keeps converting to decimal

    you could also just add a space at the start

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1