Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I can't figure out how to do custom formating 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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.