+ Reply to Thread
Results 1 to 6 of 6

Format decimal as number of months

  1. #1
    Registered User
    Join Date
    07-24-2019
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Format decimal as number of months

    Hey everyone, I want to format a decimal/fraction as a number of months. For example, I'd like to enter "=1/12" into a cell and have it show "1 month," or enter "=1.5/12" and have it show "1.5 months."

    Is this possible?

    Thanks!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Format decimal as number of months

    without VBA you cannot do that, you can use a formula to search for "/" and return everything to the left of it and append month or months to it but that would be in a different cell.

    Oh and BTW, if you enter =1/12 into a cell excel will see the equal sign and automatically consider it a division so that would complicate doing anything with a formula as you'd have to enter 1/12 without the equal sign to do what I mentioned above.
    Last edited by Sam Capricci; 04-11-2020 at 10:15 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    07-24-2019
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Format decimal as number of months

    Right, so basically I want to be able to type =1/12, and then format the cell as 12x&" months" whatever the decimal solution is.

    I.e. .083 would show up as "1 month"

    If that's not possible--understood.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Format decimal as number of months

    As was pointed out, of you type =1/12, excel will perform the calc, and I can think of no way to do this in the same cell. Other than using VBA, you MIOGHT ne able to replicate this if you create a small table and use a vlookup to return the answer - and that wont be in the same cell.
    L
    M
    3
    0.083333
    1
    4
    0.166667
    2
    5
    0.25
    3
    6
    0.333333
    4
    7
    0.416667
    5
    8
    0.5
    6
    9
    0.583333
    7
    10
    0.666667
    8
    11
    0.75
    9
    12
    0.833333
    10
    13
    0.916667
    11
    14
    1
    12
    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
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Format decimal as number of months

    So I am guessing a VBA solution is not something you can use. What about just entering the months directly (no equal sign, no division by 12, just the number of months) and then in any cells that need to use it, do your division by 12 there? You can then use this cell format to append your month designations...

    [=1]General" month";General" months"
    Last edited by Rick Rothstein; 04-11-2020 at 11:52 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Format decimal as number of months

    It looks like your goal is to have the actual number in the cell represent a fraction of a year, is that right?

    I could see using something based on the DOLLARDE() and DOLLARFR() functions. Enter months like 0.015 would be 1.5 months, then use the DOLLARDE() function to convert to the actual fraction of a year =DOLLARDE(0.015,12) would return 0.125 (1/8th of a year).
    input -- dollarde(input,12)
    0.01 -- 0.08333
    0.015 -- 0.125
    0.04 -- 0.33333
    0.09 -- 0.75
    0.11 -- .91667
    1.015 -- 1.125
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Convert Months in Decimal Format to Historical Date
    By sbattle18 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2020, 10:21 PM
  2. [SOLVED] Number Format for Decimal Place
    By sumonrezadu in forum Excel General
    Replies: 2
    Last Post: 05-15-2014, 04:14 PM
  3. Vba to format number of decimal places dependant on number in another cell
    By wonderdunder in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2012, 09:33 AM
  4. [SOLVED] Decimal Number Format in ComboBox
    By Drummer361 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2006, 05:25 PM
  5. Number format and decimal positions
    By Neal Zimm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2005, 10:07 PM
  6. Replies: 8
    Last Post: 04-20-2005, 12:06 PM

Tags for this Thread

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