Converting Decimals to Age

[annedavidd]

Hi. I am trying to convert 9.10 to 9 years 10 months but the result is showing 9 years 1 month. Please help me.

 

[Sektor]

You need VBA or formula?

 

[annedavidd]

Formula on excel Sir.

 

[AlanY]

try this

=TRIM(LEFT(SUBSTITUTE(TEXT(A1,"#0.00"),".",REPT(" ",500)),500))&" years"&TRIM(RIGHT(SUBSTITUTE(TEXT(A1,"#0.00"),".",REPT(" ",500)),500))&" month"

 

[Jimbola]

Hi. I am trying to convert 9.10 to 9 years 10 months but the result is showing 9 years 1 month. Please help me.

You're question is confusing. If your 9.10 is as decimal as your title states then that would be 9 years and 1 month, as .1 of a year is 1.2 months. If the 9.1 is decimal then you need the formula;

=MID(A1,1,FIND(".",A1)-1)&" Year(s) and "&INT(12*(MID(A1,FIND(".",A1),99)))&" Month(s)"