## Tips & Tricks 112 - Convert a Number into Years and Months

Suppose, you have been given a number into cell A1 say 26 and you want to display it as 2 Years and 4 Months, you can use following formula -

=INT(A1/12)&" Years and "&MOD(A1,12)&" Months"

Now, an user can become more demanding and he can say that if month is less than 12, then Years should not be displayed. For example, he might say that 8 should be converted to 8 Months and it should not be shown as 0 Years and 8 Months.

In this case, the formula would be -

=IF(INT(A1/12)>0,INT(A1/12)&" Years and ","")&MOD(A1,12)&" Months"

Now 8 will be displayed as 8 Months only not as 0 Years and 8 Months.

Now, user can ask more. He can say when I give 12, it displays as 1 Years and 0 Months and he simply wants to see 1 Years only. And for 36, he wants to see only 3 Years not 3 Years 0 Months. In this case, formula will have to be tweaked more. Now, the formula becomes -

=IF(INT(A1/12)>0,INT(A1/12)&" Years ","")&IF(MOD(A1,12)=0,"",MOD(A1,12)&" Months")

Now an user can come and can ask for one last thing. He can say that if this is 1 Year or 1 Month, it should not be displayed as Years or Months as 1 is not plural. Hence, 25 should be displayed as 2 Years and 1 Month not as 2 Years and 1 Months. Hence, 18 should not be displayed as 1 Years and 6 Months but as 1 Year and 6 Months. Similarly 13 should be displayed as 1 Year and 1 Month not as 1 Years and 1 Months.

=IF(INT(A1/12)>0,INT(A1/12)&" Year"&IF(INT(A1/12)>1,"s","")&" and ","")&MOD(A1,12)&" Month"&IF(MOD(A1,12)>1,"s","")