Hi All,
I'm new to the forum and thought i would register for some help please. I've actually browsed this forum as a guest for many years - but now im stuck and need assistance.
Basically, ive been asked at work to produce an excel spreadsheet to help work out payments for subscription services we offer.
The data shows the following:
Customer Name
Registration Date
No. Of Users (software license related)
No. Of Companies (as above)
Account Status (Active or Cancelled)
Cancellation Date
The above is the raw data that will be updated on a regular basis.
Now what i have so far for the formulas is:
1. It works out the payment due for the full month - depending on what their software license number is (users/companies)
2. It works out an initial pro-rata payment based on the above (1). For example - 1 full month fee is £10. If they start their contract mid-month - this would show as £5 etc...
3. It has flags to indicate if the unit is active or cancelled
4. If the unit is cancelled, it will show how many days into the month they cancelled
5. It works out a pro-rata style cancellation payment if cancelled mid-month
6. If the unit is active it will only show the pro-rata active payment and the normal full months active payment.
Now what i need help with is the following:
If a unit is cancelled, i would love it to show the following:
1. What the initial pro-rata active payment is (only if the unit cancels in the same month it was sold)
2. If a unit cancels the following month - i only need it to display what the pro-rata cancellation payment is (eg unit was sold in July but cancelled the following month)
I would love to attach my template but as per forum rules i cannot.
Here is a table of how things look:
[TABLE="width: 1706"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Customer Name[/TD]
[TD]Registration Date[/TD]
[TD]Users[/TD]
[TD]Companies[/TD]
[TD]Account Status[/TD]
[TD]Cancelleation Date[/TD]
[TD]Days In Month Activated (incl Registration date)[/TD]
[TD]End Of Month When Unit Activated[/TD]
[TD]Start Of Month When Unit Cancelled[/TD]
[TD]Active Flag[/TD]
[TD]Cancelled Flag[/TD]
[TD]In-Month Cancelled Days[/TD]
[TD]First Month's Active Pro-Rata Payment[/TD]
[TD]First Month's Cancelled Pro-Rata Payment[/TD]
[TD]Full Month Loyalty If Active[/TD]
[TD]Full Month Loyalty If Cancelled[/TD]
[TD]In-Month Cancellation Payment[/TD]
[TD]Total Payment Due If Active[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.20[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.70[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD="align: right"]13/01/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£13.80[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£35.90[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD="align: right"]14/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.15[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£16.55[/TD]
[/TR]
[TR]
[TD]Customer D[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£18.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£40.26[/TD]
[/TR]
[TR]
[TD]Customer E[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.34[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£11.84[/TD]
[/TR]
[TR]
[TD]Customer F[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£18.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£40.26[/TD]
[/TR]
[TR]
[TD]Customer G[/TD]
[TD="align: right"]07/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£15.98[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£38.08[/TD]
[/TR]
[TR]
[TD]Customer H[/TD]
[TD="align: right"]18/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£7.99[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£30.09[/TD]
[/TR]
[TR]
[TD]Customer I[/TD]
[TD="align: right"]11/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£15.26[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£37.36[/TD]
[/TR]
[TR]
[TD]Customer J[/TD]
[TD="align: right"]12/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£14.53[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£36.63[/TD]
[/TR]
[TR]
[TD]Customer K[/TD]
[TD="align: right"]19/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£9.45[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£31.55[/TD]
[/TR]
[TR]
[TD]Customer L[/TD]
[TD="align: right"]16/04/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30/04/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£10.90[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£33.00[/TD]
[/TR]
[TR]
[TD]Customer M[/TD]
[TD="align: right"]18/04/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]30/04/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£9.45[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£31.55[/TD]
[/TR]
[TR]
[TD]Customer N[/TD]
[TD="align: right"]27/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£3.63[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£25.73[/TD]
[/TR]
[TR]
[TD]Customer O[/TD]
[TD="align: right"]18/05/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31/05/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£2.99[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£9.49[/TD]
[/TR]
[TR]
[TD]Customer P[/TD]
[TD="align: right"]29/05/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]16/06/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]31/05/2017[/TD]
[TD="align: right"]01/06/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.64[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£3.21[/TD]
[TD="align: right"]£10.35[/TD]
[/TR]
[TR]
[TD]Customer Q[/TD]
[TD="align: right"]03/06/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.98[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.48[/TD]
[/TR]
[TR]
[TD]Customer R[/TD]
[TD="align: right"]05/06/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.56[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.06[/TD]
[/TR]
[TR]
[TD]Customer S[/TD]
[TD="align: right"]17/06/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.58[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£14.30[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£20.88[/TD]
[/TR]
[TR]
[TD]Customer T[/TD]
[TD="align: right"]04/07/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£13.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£14.30[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£27.46[/TD]
[/TR]
[TR]
[TD]Customer U[/TD]
[TD="align: right"]06/07/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£8.89[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£19.29[/TD]
[/TR]
[TR]
[TD]Customer V[/TD]
[TD="align: right"]16/07/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.47[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£15.87[/TD]
[/TR]
[TR]
[TD]Customer W[/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£25.64[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£26.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£51.64[/TD]
[/TR]
[TR]
[TD]Customer X[/TD]
[TD="align: right"]04/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]20/08/2017[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£23.93[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£26.00[/TD]
[TD="align: right"]£16.24[/TD]
[TD="align: right"]£66.18[/TD]
[/TR]
[TR]
[TD]Customer Y[/TD]
[TD="align: right"]18/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]28/08/2017[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD]Customer Z[/TD]
[TD="align: right"]27/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]30/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£3.63[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£21.07[/TD]
[TD="align: right"]£46.80[/TD]
[/TR]
</tbody>[/TABLE]
I really hope someone can help me finish off this project, appreciated in advance.
Marc
I'm new to the forum and thought i would register for some help please. I've actually browsed this forum as a guest for many years - but now im stuck and need assistance.
Basically, ive been asked at work to produce an excel spreadsheet to help work out payments for subscription services we offer.
The data shows the following:
Customer Name
Registration Date
No. Of Users (software license related)
No. Of Companies (as above)
Account Status (Active or Cancelled)
Cancellation Date
The above is the raw data that will be updated on a regular basis.
Now what i have so far for the formulas is:
1. It works out the payment due for the full month - depending on what their software license number is (users/companies)
2. It works out an initial pro-rata payment based on the above (1). For example - 1 full month fee is £10. If they start their contract mid-month - this would show as £5 etc...
3. It has flags to indicate if the unit is active or cancelled
4. If the unit is cancelled, it will show how many days into the month they cancelled
5. It works out a pro-rata style cancellation payment if cancelled mid-month
6. If the unit is active it will only show the pro-rata active payment and the normal full months active payment.
Now what i need help with is the following:
If a unit is cancelled, i would love it to show the following:
1. What the initial pro-rata active payment is (only if the unit cancels in the same month it was sold)
2. If a unit cancels the following month - i only need it to display what the pro-rata cancellation payment is (eg unit was sold in July but cancelled the following month)
I would love to attach my template but as per forum rules i cannot.
Here is a table of how things look:
[TABLE="width: 1706"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Customer Name[/TD]
[TD]Registration Date[/TD]
[TD]Users[/TD]
[TD]Companies[/TD]
[TD]Account Status[/TD]
[TD]Cancelleation Date[/TD]
[TD]Days In Month Activated (incl Registration date)[/TD]
[TD]End Of Month When Unit Activated[/TD]
[TD]Start Of Month When Unit Cancelled[/TD]
[TD]Active Flag[/TD]
[TD]Cancelled Flag[/TD]
[TD]In-Month Cancelled Days[/TD]
[TD]First Month's Active Pro-Rata Payment[/TD]
[TD]First Month's Cancelled Pro-Rata Payment[/TD]
[TD]Full Month Loyalty If Active[/TD]
[TD]Full Month Loyalty If Cancelled[/TD]
[TD]In-Month Cancellation Payment[/TD]
[TD]Total Payment Due If Active[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.20[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.70[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD="align: right"]13/01/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£13.80[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£35.90[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD="align: right"]14/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.15[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£16.55[/TD]
[/TR]
[TR]
[TD]Customer D[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£18.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£40.26[/TD]
[/TR]
[TR]
[TD]Customer E[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.34[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£11.84[/TD]
[/TR]
[TR]
[TD]Customer F[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£18.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£40.26[/TD]
[/TR]
[TR]
[TD]Customer G[/TD]
[TD="align: right"]07/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£15.98[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£38.08[/TD]
[/TR]
[TR]
[TD]Customer H[/TD]
[TD="align: right"]18/02/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28/02/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£7.99[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£30.09[/TD]
[/TR]
[TR]
[TD]Customer I[/TD]
[TD="align: right"]11/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£15.26[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£37.36[/TD]
[/TR]
[TR]
[TD]Customer J[/TD]
[TD="align: right"]12/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£14.53[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£36.63[/TD]
[/TR]
[TR]
[TD]Customer K[/TD]
[TD="align: right"]19/03/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]31/03/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£9.45[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£31.55[/TD]
[/TR]
[TR]
[TD]Customer L[/TD]
[TD="align: right"]16/04/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30/04/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£10.90[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£33.00[/TD]
[/TR]
[TR]
[TD]Customer M[/TD]
[TD="align: right"]18/04/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]30/04/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£9.45[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£31.55[/TD]
[/TR]
[TR]
[TD]Customer N[/TD]
[TD="align: right"]27/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£3.63[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£25.73[/TD]
[/TR]
[TR]
[TD]Customer O[/TD]
[TD="align: right"]18/05/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31/05/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£2.99[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£9.49[/TD]
[/TR]
[TR]
[TD]Customer P[/TD]
[TD="align: right"]29/05/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]16/06/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]31/05/2017[/TD]
[TD="align: right"]01/06/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.64[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£3.21[/TD]
[TD="align: right"]£10.35[/TD]
[/TR]
[TR]
[TD]Customer Q[/TD]
[TD="align: right"]03/06/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.98[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.48[/TD]
[/TR]
[TR]
[TD]Customer R[/TD]
[TD="align: right"]05/06/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.56[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£6.50[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£12.06[/TD]
[/TR]
[TR]
[TD]Customer S[/TD]
[TD="align: right"]17/06/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]30/06/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£6.58[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£14.30[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£20.88[/TD]
[/TR]
[TR]
[TD]Customer T[/TD]
[TD="align: right"]04/07/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£13.16[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£14.30[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£27.46[/TD]
[/TR]
[TR]
[TD]Customer U[/TD]
[TD="align: right"]06/07/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£8.89[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£19.29[/TD]
[/TR]
[TR]
[TD]Customer V[/TD]
[TD="align: right"]16/07/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]31/07/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£5.47[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£10.40[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£15.87[/TD]
[/TR]
[TR]
[TD]Customer W[/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD]Active[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/01/2099[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]£25.64[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£26.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£51.64[/TD]
[/TR]
[TR]
[TD]Customer X[/TD]
[TD="align: right"]04/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]20/08/2017[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£23.93[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£26.00[/TD]
[TD="align: right"]£16.24[/TD]
[TD="align: right"]£66.18[/TD]
[/TR]
[TR]
[TD]Customer Y[/TD]
[TD="align: right"]18/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]28/08/2017[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD]Customer Z[/TD]
[TD="align: right"]27/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]30/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£3.63[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£22.10[/TD]
[TD="align: right"]£21.07[/TD]
[TD="align: right"]£46.80[/TD]
[/TR]
</tbody>[/TABLE]
I really hope someone can help me finish off this project, appreciated in advance.
Marc
