 SOLVED

# Removing decimals in a MOD formula

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? 3 Replies
best response confirmed by LL19911695 (Occasional Contributor)
Solution

# Re: Removing decimals in a MOD formula

@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"

# Re: Removing decimals in a MOD formula

That worked, thank you

# Re: Removing decimals in a MOD formula

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