Date format

[jmazorra]

Hello

Earlier a user helped me with the formula below. It works great, however, the formula's result is a decimal number which needs to be turned into years and months. The formula lies on cell U31 and U30, In U30 I have:

=IF(AVERAGE(IF(K6:K126="y",IF(R6:R126>=1,B6:B126))),AVERAGE(IF(K6:K126="y",IF(R6:R126>=1,B6:B126))),0)

and in U31 I have the following formula:

=AVERAGE(IF('All Employees Annualized'!R6:R127>=1,'All Employees Annualized'!B6:B127),0)

I have to hide this column since I need to see the data in both cells to show in years and months. So, on cell U28 and U27 I have the following formula to turn the decimal into years and months:

=IF(INT(U31),INT(U31)&" Yrs "," 0 Yrs ") &IF(MOD(U31,1),ROUND(12*MOD(U31,1),0) & " Mths"," 0 Mths")

which gives the result as ie: 1 yrs 7 mths

Questions is, is there a way to combine both formulas so that I don't have to hide a column, or is the way I am doing it the only way:

 

[shg]

I would keep the approach you have.

You could change the second formula to

=TEXT(100*dollarfr(U31, 12), "0 ""Yrs "" 00 ""Mths""")

 

[Biz]

I would keep the approach you have.

You could change the second formula to

=TEXT(100*dollarfr(U31, 12), "0 ""Yrs "" 00 ""Mths""")

Nice 1

Biz