Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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".

share|improve this question

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.

share|improve this answer

You could use a formula along these lines which splits the input based on the position for 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.

share|improve this answer

You must log in to answer this question.

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