+ 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
    Office 365 | 2016
    Posts
    567

    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
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,274

    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
    49,761

    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 Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Ratio keeps converting to decimal

    You could also precede the string with an apostrophe (')

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,274

    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
    49,761

    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)

Similar Threads

  1. How to calculate a number based on the given ratio. For example a 2:3 ratio.
    By Ayoub99k in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-11-2015, 08:15 AM
  2. Replies: 2
    Last Post: 12-22-2011, 11:38 AM
  3. Numerical Ratio to Return Decimal Equivalent
    By jevery in forum Excel General
    Replies: 2
    Last Post: 12-11-2009, 08:34 PM
  4. [SOLVED] Divide x/y should give Ratio x:y,How to express ratio in excel
    By arvind3738 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-10-2006, 03:29 AM
  5. Replies: 3
    Last Post: 07-05-2006, 01:57 PM
  6. Replies: 3
    Last Post: 03-18-2006, 02:25 PM
  7. Converting Decimal Ft into Ft & In
    By Edward McClelland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2006, 11:15 AM

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