0

I can't figure out how to do custom formatting that would format input "1/5" as "1 year, 5 months", or "5,0" as "5 years". I do want the cell to retain original input of "1/5" but visually display "1 year, 5 months".

3 Answers 3

2

You could use a formula along these lines which splits the input based on the position of the / character:

=LEFT(A1, FIND("/", A1)-1) & " years, " & RIGHT(A1, LEN(A1)-FIND("/", A1)) & " months"

assuming that a text value such as 1/5 is in cell A1.

1
  • Your answer is not addressing the Question,,, OP wants to apply Cell Format to cell has 1/5 or 5,0 to show Years & Months in that case how this Formula will works?? Mar 29, 2019 at 11:20
1

Firstly, this solution is flawed in the sense that it requires leading 0's for months and will only take input as yymm (without the / or ,), also it will not change the pluralization of years/months based on the number you have entered. Neither will it omit years or months if only one is entered.

Without further adiou however, here is my solution;

First select the cell(s) you wish to format. Right-click and select "Format Cells...". On the 'Number' tab select custom as your category. Type the string below into the 'type' field.

00 "Year(s)," 00 "Month(s)"

You can change the quoted strings to anything you wish, as long as you retain the quotation marks.

To test, enter a few tests;

If you enter 3445 you will get "34 Year(s), 45 Month(s)"
If you enter 45 you will get "00 Year(s), 45 Month(s)"
If you enter 405 you will get "04 Year(s), 05 Month(s)"

This has been tested in Excel 2010 and 2003.

5
  • it's not working as universal rule, suppose I write 35 then getting 0 years and 35 Moths instead of 3 Years and 5 months!! Mar 29, 2019 at 11:17
  • Your Answer needs little correction it Should be 0 "Year(s)," 0 "Month(s)" then works nicely! Mar 29, 2019 at 11:33
  • @RajeshS I addressed that in my test cases. If you want to display 3 years 5 months, you must enter 305 instead of 35. I haven't tested it, but I believe your correction will make this not work for double-digit months, e.g. 12 should represent 00 years 12 months, not 1 years 2 months.
    – Dandamann
    Mar 30, 2019 at 20:45
  • "0 "Year(s)," 0 "Month(s)" returns 1 Year(s) , 2 Month(s) for 12. I've tested it. The right most value always represents Month. 202 will 20 year(s), 2 Month(s), but this `00 "Year(s)," 00 "Month(s)", returns 02 Year(s), 02 Month(s). ☺ Mar 31, 2019 at 4:57
  • 1
    You aren't understanding my point. How would you represent 0 years 12 months with your solution?
    – Dandamann
    Apr 1, 2019 at 5:24
1

you can use below formula to show numbers/decimal numbers as years/months. this formula wil work 2007 excel version or later version.

=INT(a1) & " years / " & INT((a1-INT(a1))*12) & " months"

2
  • 1
    Your answer is not addressing the Question,,, you want to apply Cell Format to cell has 1/5 or 5,0 to show Years & Months !! Mar 29, 2019 at 11:18
  • If you actually try your formula, you will see that it produces the wrong answer for number of months.
    – fixer1234
    Mar 29, 2019 at 19:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .