• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Months Remaining on Amort Schedule

jdavis

New Member
Need some assistance. The Mos. Remaining number goes negative when the Period Ending date exceeds the Date To. How do I stop this and return the number "0" when this happens?
 

Attachments

  • Working Formulas for 14045.xlsx
    432 KB · Views: 0
In your file, which are the various dates, there are three sets of dates, B2 and column E and Column F; which is who what why and when?
 
Hi and welcome to the forum :awesome:

Not sure about your exact requirement, but I have tried something and slightly modified one of your formula, can you check this:

=IF(E5>=$B$2,0,IF((YEAR(F5)-YEAR($B$2))*12+(MONTH(F5)-MONTH($B$2))<0,0,(YEAR(F5)-YEAR($B$2))*12+MONTH(F5)-MONTH($B$2)))

Regards,
 
Sorry, the headers are not above the cells! Here's a new file with headers
Hi and welcome to the forum :awesome:

Not sure about your exact requirement, but I have tried something and slightly modified one of your formula, can you check this:

=IF(E5>=$B$2,0,IF((YEAR(F5)-YEAR($B$2))*12+(MONTH(F5)-MONTH($B$2))<0,0,(YEAR(F5)-YEAR($B$2))*12+MONTH(F5)-MONTH($B$2)))

Regards,
I get the same results with my formula. The manually input numbers are what I am looking for (see yellow section in new upload file). The numbers in the section, Row 15-59 under Mos Remaining are the correct result. The Mos Remaining number represents the months remaining to amortize a certain number based on the months remaining when compared to the Period Ending date. If the Date From and the Date To are outside the Period Ending date then the number returned should be the difference between Date From and Date To.
 

Attachments

  • Working Formulas for 14045.xlsx
    433.6 KB · Views: 0
  • Working Formulas for 14045.xlsx
    435 KB · Views: 2
try, in row 5:
=IFERROR(DATEDIF(MAX($C$4,F5),G5+1,"M"),0)
and copy down.
There is one date the 28th Feb 2016, this is not the end of the month so datedif (which counts complete months) gives one complete month less than what you seem to want. Rows 8 and 18.
 
Last edited:
try, in row 5:
=IFERROR(MIN(DATEDIF($C$4,G5+1,"M"),DATEDIF(F5,G5+1,"M")),0)
and copy down.
There is one date the 28th Feb 2016, this is not the end of the month so datedif (which counts complete months) gives one complete month less than what you seem to want. Rows 8 and 18.
I changed the month end date and the formula works. Thank you very much.
 
Back
Top