Direct Debit Fee Completion Date - Month Date Formula relevant to the DD Date i.e. 28th Month

[Stef56]

I would like to return the finish date of a Direct Debit for Customers, when the Direct Debit due date varies within a month. No problems for weekly or fortnightly. However if it will take 48.6 payments to finish the "loan" I would like the finish date to be the 28th (or 15th) of the relevant month. At the moment I'm using 30 days to approximate the finish date, but would like it to be more accurate. Here is an example of what I'm doing:

[TABLE="width: 786"]
<colgroup><col><col><col><col span="2"><col><col><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 786"]
<colgroup><col><col><col><col span="2"><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Outstanding[/TD]
[TD][/TD]
[TD]Instalments[/TD]
[TD]Calculated[/TD]
[TD]Nominated[/TD]
[TD]Insufficient Nominated[/TD]
[TD]No. of Instalments[/TD]
[TD]Remaining Instal.[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Balance[/TD]
[TD][/TD]
[TD]Remaining[/TD]
[TD]Instalment[/TD]
[TD]Instalment[/TD]
[TD]Amount Warning[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]31/12/2017[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AS AT[/TD]
[TD="align: right"]28/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$465.00[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$232.50[/TD]
[TD]$465.00[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27/11/2016[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$607.25[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$303.63[/TD]
[TD]$272.00[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2/01/2017[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]63.25[/TD]
[/TR]
[TR]
[TD]$129.99[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$65.00[/TD]
[TD]$129.99[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27/11/2016[/TD]
[TD="align: right"]129.99[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$1,331.00[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$665.50[/TD]
[TD]$266.00[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]27/03/2017[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]799[/TD]
[/TR]
[TR]
[TD]$3,404.26[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$1,702.13[/TD]
[TD]$304.23[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28/09/2017[/TD]
[TD="align: right"]608.46[/TD]
[TD="align: right"]2795.8[/TD]
[/TR]
[TR]
[TD]$64.99[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$32.50[/TD]
[TD]$64.99[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27/11/2016[/TD]
[TD="align: right"]64.99[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$1,005.54[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$502.77[/TD]
[TD]$499.94[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]27/12/2016[/TD]
[TD="align: right"]1005.54[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$455.00[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$227.50[/TD]
[TD]$305.00[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/12/2016[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$800.00[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$400.00[/TD]
[TD]$396.25[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]27/12/2016[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]$220.00[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$110.00[/TD]
[TD]$30.00[/TD]
[TD]*** Warning ****[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5/06/2017[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]160[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
As you can see I'm also calculating how much of the direct debit payment schedule is being paid in each calendar year until it finishes. Any help would be appreciated.

 

[Stef56]

Ok I have watched Excel Magic Trick 783 and realise I can use : [TABLE="width: 666"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=today ()[/TD]
[/TR]
[TR]
[TD]=DATE(YEAR(d1),MONTH(d1),28) in another column or combine the formula somehow.

All good.

[/TD]
[/TR]
</tbody>[/TABLE]