Removing decimals in a MOD formula

[LL19911695]

I am trying to create a formula which returns the average length of time in years and months from a data set. 

• In column Z of my dataset I have lengths of time in months.
• In a new cell on a separate tab I have the average of these lengths of time (pulled by the =AVERAGE formula), again in months
• In the cell next to this one I have a formula to convert this average length of time (in months) into years and months: =INT(B6/12) & " years and " &MOD(B6,12)& " months"

My problem is, this final formula is coming back with multiple decimal places for the months portion (see below.) Is there any way I can get it to pull back the months portion with no decimals, just as it does for the years portion?

[#2 · Accepted solution]

@LL19911695 Like this perhaps?

=INT(B6/12) & " years and " & TEXT(MOD(B6,12),"0") & " months"

Alternatively:

=INT(B6/12) & " years and " & ROUND(MOD(B6,12),0) & " months"

[#3]

That worked, thank you :)

[#4]

@LL19911695 Obviously, you can also wrap the MOD function in INT, similar to the year portion. Though, that would result in 9.