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!
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!
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
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.
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
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.
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
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks