Convert # of months into Years and Months

[vanhalen5_2000]

If I have a cell that has a number, say 59. I would like to convert that in another cell into years and months, for example 59 months would equal 4 years 11 months. Is there a way to display that as 4.11
Thanks

 

[iggydarsa]

=int(a1/12) &"."& Mod(a1, 12)

 

[Jonmo1]

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

 

[VoG]

Or

=INT(A1/12)+(A1-INT(A1/12)*12)/100

 

[vanhalen5_2000]

Wow
you guys are fast.
Thanks Alot!!!

 

[sucett]

like this one better coz you can still with the result as a number in case you need to do any math with that later on... plus it is shorter!

 

[Jonmo1]

vanhalen5_2000,

I've got Floor Tickets to Van Halen In Houston Jan 28th. Unreal.

 

[ajm991]

very old thread, but I want the same thing - BUT - my cell already has a formula saying =(YEAR(B5)-YEAR(E5))*12+MONTH(B5)-MONTH(E5). How can I edit it so I get years and months here in this cell?

 

[soong]

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

This worked for me unless the difference was a negative and then it went a bit wrong.

Any ideas?

 

[Tetra201]

If you want to see "-1 Year and 3 Month", try this:

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

If you want to see "-1 Year and -3 Month", try this:

=TRUNC(A1/12) & " Years and " &SIGN(A1)*MOD(ABS(A1),12)& " Months"