SOLVED

Removing decimals in a MOD formula

Occasional Contributor

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?

 

LL19911695_0-1655886198158.png

 

3 Replies
best response confirmed by LL19911695 (Occasional Contributor)
Solution

@LL19911695 Like this perhaps?

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

 

Riny_van_Eekelen_0-1655886874814.png

Alternatively:

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

 

That worked, thank you

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