Been trying to do this by the use of excel formulas and getting in a
big mess.

I have a spreadsheet which displays the payments for each customer.
(See table1)
What I would like is Excel VBA that goes through the table1 details, to
display the next payment for a given year. (See table2) The first
payment date is fixed on a payment date cycle e.g. If the Firth_Payment
date field specifies 2nd day, then the payment will be made on the
second day for that month, quarterly or semi annually event.

table1
Customer Duration First_Payment
1 Monthly 02-Feb-2001
2 Monthly 16-Jan-2002
3 Monthly 02-Oct-2003
4 Monthly 05-May-2004
5 Monthly 07-Aug-2005
6 Quarterly 17-Feb-1998
7 Quarterly 30-Mar-2003
8 Quarterly 30-Mar-2004
9 Quarterly 25-Mar-1999
10 Quarterly 03-Oct-2005
11 Quarterly 06-Oct-2005
12 Quarterly 12-Oct-2005
13 Semi-Annually 01-Jul-1997
14 Semi-Annually 06-Apr-2001
15 Semi-Annually 30-Nov-2005
16 Semi-Annually 31-Dec-2008

table2:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
02/01 02/02 02/03 02/04 02/05 02/06 02/07 02/08 02/09 02/10 02/11 02/12
16/01 16/02 16/03 16/04 16/05 16/06 16/07 16/08 16/09 16/10 16/11 16/12
02/01 02/02 02/03 02/04 02/05 02/06 02/07 02/08 02/09 02/10 02/11 02/12
05/01 05/02 05/03 05/04 05/05 05/06 05/07 05/08 05/09 05/10 05/11 05/12
07/01 07/02 07/03 07/04 07/05 07/06 07/07 07/08 07/09 07/10 07/11 07/12
17/02 17/05 17/08 17/11
30/03 30/06 30/09 30/12
30/03 30/06 30/09 30/12
25/03 25/06 25/09 25/12
03/01 03/04 03/07 03/10
06/01 06/04 06/07 06/10
12/01 12/04 12/07 12/10
01/01 01/07
06/04 06/10
30/05 30/11
30/06 31/12


Conditions:
Monthly Payments: If the record is monthly, there will be a monthly
payment thoughout the year and the date is based on the First_Payment
date day. For example, take the first payment date record 02-Feb-2001
and for the period 2005 table, will generate a for each month a date
02/02 and 02/02 and 02/03 ....02/12. The difference between the each
payments is roughly 1 month.

Quarterly Payments: If the record is quarterly, there will be 4
payments for the year. Space out into 4 payments, the First Payment
field will be used to determine when the payment month should be made.
For example: First Payment record 17-Feb-1998, will have four payments
and they will fall in 17/02,17/05,17/08 and 17/11. Another example
would be, 03/10/2005 would create 03/01, 03/04,03/07 and 03/10. The
difference between the each payments is roughly 3 months

Jan,Apr,Jul,Oct
Feb,May,Aug,Nov
Mar,Jun,Sep,Dec

All above = rotational.

Semi-Annual Payments:If the record is Semi-Annual, there will be 2
payments. For example,06/04/2001 would create 06/04 and 06/10. another
example, 30-Nov-05 would create 05/11 and 30/11. The difference between
the each payments is roughly 6 months.


Jan,Jul
Feb,Aug
Mar,Sep
Apr,Oct
May,Nov
Jun,Dec
All above = Vice versa.

Points to consider: If the First_Payment day falls on the 31st and ends
up in a month that has less than 30 days, then the payment would be
made on the last day of that month. For example, 31st would be 30th in
June or 28th in Feb... and so on....


Appreciate any help on this matter. Somehow this is more difficult
than Sudoku.

Brenda Dancer "xxx"

Here's a different approach:

1. I copied your data from Table1 into a new sheet, range A1.
2. I Named the Data only (A3:C18) : Tbl1
3. In F1: J1 I entered some labels:
F1 - Customer
G1 - Duration
H1 - Monthly
I1 - Quarterly
J1 - Semi-Annual
4. I Named
F2 - Cust
G2 - Dur
5 I entered the following formulas:

H2 =IF(Dur="Monthly",VLOOKUP(Cust,Tbl1,3,FALSE),"")

H3 =IF(H2="","",IF(MONTH(H2)=MONTH(H2+1),DATE(YEAR(H2 ),MONTH(H2)+1,DAY(H2)),DATE(YEAR(H2),MONTH(H2)+2,1 )-1))

H4: H?? Copy H3 down as far as needed

I2 =IF(Dur="Quarterly",VLOOKUP(Cust,Tbl1,3,FALSE),"")
I3
=IF(I2="","",IF(MONTH(I2)=MONTH(I2+1),DATE(YEAR(I2 ),MONTH(I2)+3,DAY(I2)),DATE(YEAR(I2),MONTH(I2)+4,1 )-1))
I4: I??? Copy I3 down as far as needed

J2 =IF(Dur="Semi-Annually",VLOOKUP(Cust,Tbl1,3,FALSE),"")
J3
=IF(J2="","",IF(MONTH(J2)=MONTH(J2+1),DATE(YEAR(J2 ),MONTH(J2)+6,DAY(J2)),DATE(YEAR(J2),MONTH(J2)+7,1 )-1))
J4:J?? Copy J3 down as far as needed

Once this is set up all you need to do is enter the Customer number
in F2 and the pmt schedule will be generated for that customer in
either col H,I or J depending on the duration.

You will note that I have handled the "Last Day of the Mo" issue
by determining first whether any pmt date falls on the Last day
of the month.
The effect of this is that if a pmt starts with Feb 28, then the next pmt
date is Mar 31 --not Mar28. You may want to tweak this.
Hope this helps get you started



" wrote:

Been trying to do this by the use of excel formulas and getting in a
big mess.

I have a spreadsheet which displays the payments for each customer.
(See table1)
What I would like is Excel VBA that goes through the table1 details, to
display the next payment for a given year. (See table2) The first
payment date is fixed on a payment date cycle e.g. If the Firth_Payment
date field specifies 2nd day, then the payment will be made on the
second day for that month, quarterly or semi annually event.

table1
Customer Duration First_Payment
1 Monthly 02-Feb-2001
2 Monthly 16-Jan-2002
3 Monthly 02-Oct-2003
4 Monthly 05-May-2004
5 Monthly 07-Aug-2005
6 Quarterly 17-Feb-1998
7 Quarterly 30-Mar-2003
8 Quarterly 30-Mar-2004
9 Quarterly 25-Mar-1999
10 Quarterly 03-Oct-2005
11 Quarterly 06-Oct-2005
12 Quarterly 12-Oct-2005
13 Semi-Annually 01-Jul-1997
14 Semi-Annually 06-Apr-2001
15 Semi-Annually 30-Nov-2005
16 Semi-Annually 31-Dec-2008

table2:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
02/01 02/02 02/03 02/04 02/05 02/06 02/07 02/08 02/09 02/10 02/11 02/12
16/01 16/02 16/03 16/04 16/05 16/06 16/07 16/08 16/09 16/10 16/11 16/12
02/01 02/02 02/03 02/04 02/05 02/06 02/07 02/08 02/09 02/10 02/11 02/12
05/01 05/02 05/03 05/04 05/05 05/06 05/07 05/08 05/09 05/10 05/11 05/12
07/01 07/02 07/03 07/04 07/05 07/06 07/07 07/08 07/09 07/10 07/11 07/12
17/02 17/05 17/08 17/11
30/03 30/06 30/09 30/12
30/03 30/06 30/09 30/12
25/03 25/06 25/09 25/12
03/01 03/04 03/07 03/10
06/01 06/04 06/07 06/10
12/01 12/04 12/07 12/10
01/01 01/07
06/04 06/10
30/05 30/11
30/06 31/12


Conditions:
Monthly Payments: If the record is monthly, there will be a monthly
payment thoughout the year and the date is based on the First_Payment
date day. For example, take the first payment date record 02-Feb-2001
and for the period 2005 table, will generate a for each month a date
02/02 and 02/02 and 02/03 ....02/12. The difference between the each
payments is roughly 1 month.

Quarterly Payments: If the record is quarterly, there will be 4
payments for the year. Space out into 4 payments, the First Payment
field will be used to determine when the payment month should be made.
For example: First Payment record 17-Feb-1998, will have four payments
and they will fall in 17/02,17/05,17/08 and 17/11. Another example
would be, 03/10/2005 would create 03/01, 03/04,03/07 and 03/10. The
difference between the each payments is roughly 3 months

Jan,Apr,Jul,Oct
Feb,May,Aug,Nov
Mar,Jun,Sep,Dec

All above = rotational.

Semi-Annual Payments:If the record is Semi-Annual, there will be 2
payments. For example,06/04/2001 would create 06/04 and 06/10. another
example, 30-Nov-05 would create 05/11 and 30/11. The difference between
the each payments is roughly 6 months.


Jan,Jul
Feb,Aug
Mar,Sep
Apr,Oct
May,Nov
Jun,Dec
All above = Vice versa.

Points to consider: If the First_Payment day falls on the 31st and ends
up in a month that has less than 30 days, then the payment would be
made on the last day of that month. For example, 31st would be 30th in
June or 28th in Feb... and so on....


Appreciate any help on this matter. Somehow this is more difficult
than Sudoku.

Brenda Dancer "xxx"