Payment calculator adjustment help

I have a payment calculator that helps determine if payments are within their due dates. Payments have a twenty day grace period. You can pay twenty days before or twenty days after. My calculator makes it easier to find out what monthly due date would work for all the payments. You are permitted to miss ONE month.

Attached is my calculator. You can see on the "Classic" tab the calculator I started with versus what I have created thus far. Password is QA.

My two roadblocks are as you can see in my example workbook, if you make payments on the 15th of every month, January through June, miss a July payment, then pay on the 27th from August to December. We would first try to pick a due date that would cause you NOT to skip a month. However, in this example, there isn't a due date that would work. So you would have to just skip July's payment. Hope that makes sense. Just in case, i'll try again, if it did, skip the next paragraph lol

Do not think in terms of due dates. You make 9 payments. You want those payments to apply, without missing a month and therefore extending your time. So you need to then, based on your payment dates, pick a due date that will get your 9 payments in 9 straight months. Otherwise, you are allowed to skip ONE month.

A January 3rd payment could count as your December payment.

I have my calculator setup to roll all the payments back or forward a month, but if an actual skip month is required, I do not have a way to do that without the user actually identifying it. Basically if column E is over 20 regardless of the due date, then it would be a skip month. So if I had a button that would look through "E" and say when E7= >20, then it would change the formula in B7 to be =EDATE(B6,2),"")) instead of EDATE(B6,1),"")). Which would cause it to skip a month.


My other roadblock is right now you have to manually guess the due dates to try and find one that works. Perfect world, macro would see if a due date of the 1st would work for all, otherwise then move to due date of the 2nd.
Incorrect formula, but B19="1", See if E4 is +/- 20, then see if E5 is +/- 20........or........if but B19="1", See if E4 is +/- 20, if not then, B19="2", See if E4 is +/- 20


I don't doubt this to be an extremely difficult task, one that may not generate any response or solutions, but if anyone can help me bridge the gap..