# How to format excel cells that show duration in years and months?

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

-

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.

-

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.

-