# Calculating months based on Balance amount vs monthly deduction



## fayez_MrExcel (Jan 3, 2023)

Im tring to get the no. of months/days AND years/months  from Balance amount vs monthly deduction.  But the 2 decimal result seems not correct. 

In  D6 im expecting 145 months and 7 days, while
in D7, im expecting 12 years & 9months

Book5CDE3DetailsValueExpected Result4Monthly Deduction5,279.825Amortizaton Balance754,881.186No. of Months/days143.67145.077No. of Years / Months12.8512.09Sheet1Cell FormulasRangeFormulaD6D6=IF(MOD((D5/D4),1)>0.3,(D5/D4)+1-0.3,(D5/D4))D7:E7D7=IF(MOD((D6/12),1)>0.11,(D6/12)+1-0.12,(D6/12))



Just would like to clarify my query,  the quotient of 143.67 represent months days.  Then i'd like to convert this integer into years & months


----------



## 6StringJazzer (Jan 3, 2023)

How are you calculating your "expected result" in column E?


----------



## fayez_MrExcel (Jan 3, 2023)

6StringJazzer said:


> How are you calculating your "expected result" in column E?


id like to convert an integer into years and months if the number represents months and days. Example 143.67 (where 143 is a month and .67 is days)


----------



## Dave Patton (Jan 4, 2023)

T202301a.xlsmABCD1DetailsValue2Monthly Deduction5,279.823Amortizaton Balance754,881.184No. of Months/days142.97479529142 Months 29 days5No. of Years / Months11.91456620.9145662211 Years 10.8 Months61bCell FormulasRangeFormulaB4B4=B3/B2C4C4=INT(MOD(B4,1)*30)D4D4=INT(B4)&" Months "&INT(MOD(B4,1)*30)&" days"B5B5=B4/12C5C5=MOD(B5,1)D5D5=INT(B5)&" Years "&ROUND(MOD(B5,1),1)*12&" Months"


----------



## 6StringJazzer (Jan 4, 2023)

fayez_MrExcel said:


> id like to convert an integer into years and months if the number represents months and days. Example 143.67 (where 143 is a month and .67 is days)


Maybe my question wasn't clear. What is the arithmetic you used to get those answers?


----------

