2

If I have the following value:

18

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

1
  • 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, 2011 at 17:58

2 Answers 2

3

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

2

You can do something like this:

=INT(A1/12) & " years " & MOD(A1,12) & " months"

You must log in to answer this question.

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