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

If I have the following value:


How can I get Excel to show this as 1 year, 6 months or something similar?

share|improve this question
You know you're a *NIX programmer when you see the word excel and render it as execl. After all, people read letters, not words. – new123456 Jul 20 '11 at 17:58
up vote 3 down vote accepted

If your value is in A1, you can use the following formula to display it as years, months:

=IF(A1>11, INT(A1/12) & " year" & IF(INT(A1/12)<>1, "s", ""), "") 
& IF(MOD(A1,12) > 0, IF(A1>11, ", ", "") & MOD(A1, 12) & " month" 
& IF(MOD(A1,12)<>1, "s", ""), "")

This will show the following types of values:

1 year
6 months
1 month
1 year, 4 months
2 years, 11 months

share|improve this answer

You can do something like this:

=INT(A1/12) & " years " & MOD(A1,12) & " months"
share|improve this answer

You must log in to answer this question.

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