years and months

[asua09]

hi

How do i display 17 as 1 year 5 months, 26 months as 2 years 2months
etc etc

thanks for any help

 

[Dave R.]

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

 

[JE McGimpsey]

One way:

=INT(A1/12) & IF(INT(A1/12)<>1," years "," year ") & MOD(A1,12) &
IF(MOD(A1,12)<>1," months"," month")

 

[asua09]

Hi

Thanks for your help

 

[asua09]

thanks but how can i cope with neg numbers

-3 will not shows as 3 months (Pef in red font)

 

[JE McGimpsey]

One way:

=INT(ABS(A1)/12) & IF(INT(ABS(A1)/12)<>1," years "," year ") &
MOD(ABS(A1),12) &IF(MOD(ABS(A1),12)<>1," months"," month")

Use conditional to make the font red if A1 is negative:

CF1: Formula is =A1<0
Format: Font->Red