druddle

Hello

I am using Excel and I want to be able to take an amount of months (say, 46) and use Excel to calculate the amount of years and months that is (3 years 10 months), not how many years as a decimal (3.85).

Does anyone know how to do this ?

Dave

warrenm2

(no.of months)mod 12 for months, (no.of months)div 12 for years

LostUser

If you use A1 for the value you want to convert (e.g 46)

Use

=MOD(A1,12)

to return the number of months and

= INT(A1/12)

to return the number of years.

ash002004

iTrader: (1)

Don't you just need to highlight the cells and choose the settings to show what value/units you want to view it all in? or u asking how to work the whole formula

druddle

Thanks chaps. I have 46 in B1. In B2 i have =INT(B1/12) and that gives me 3. If I out =MOD(B1/12) in D2 I get an error saying "Youve entered too few arguments for this function).

All I want is B2 to show the years and D2 to show the months, so B2 says "3", C2 says "years", D2 says "10" and E2 says "months". I can input C2 and E2 in text but how do I get the rest to work ?

Dave

LostUser

That's cos you're putting =MOD(B1/12)

It should be =MOD(B1,12)

markr1963

Or you could be proper smart ar$e and output the lot in 1 cell taking into account 1 year, 1 month and no months

=IF(INT(B1/12)=1, INT(B1/12) & " year ",INT(B1/12) & " years ") & IF(MOD(B1,12)=0,"", IF(MOD(B1,12)=1, "and " & MOD(B1,12) & " month","and " & MOD(B1,12) & " months"))

druddle

Job done. Thanks alot.