up vote 1 down vote favorite
share [g+] share [fb]

If I have the following value:

18

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

link|improve this question
100% accept rate
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
feedback

2 Answers

up vote 2 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

link|improve this answer
feedback
up vote 2 down vote

You can do something like this:

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

Your Answer

 
or
required, but never shown
discard

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