Next Date

**kwiklearner** · 03-09-2007, 01:40 PM

I am looking for someone, anyone to help me create a formula to identify the next date. I have 3 columns of data:
A) PurchaseDate
B) PayDate
C) Payment Freq/Year

Column A = Date Purchased
Column B = First Payment Date
Column C = # of payments/Year (1,4,6,12)

Example1:
Purchase Date = 03/22/2006
Payment Date = 02/20/2004
# of payments/Year = 4
Next payment Date after purchase date = 05/20/2006

Example2:
Purchase Date = 03/20/2006
Payment Date = 05/15/2003
# of payments/Year = 12
Next payment Date after purchase date = 04/15/2006

Thanks in advance!

**starguy** · 03-10-2007, 08:46 AM

Originally Posted by kwiklearner

I am looking for someone, anyone to help me create a formula to identify the next date. I have 3 columns of data:
A) PurchaseDate
B) PayDate
C) Payment Freq/Year

Column A = Date Purchased
Column B = First Payment Date
Column C = # of payments/Year (1,4,6,12)

Example1:
Purchase Date = 03/22/2006
Payment Date = 02/20/2004
# of payments/Year = 4
Next payment Date after purchase date = 05/20/2006

Example2:
Purchase Date = 03/20/2006
Payment Date = 05/15/2003
# of payments/Year = 12
Next payment Date after purchase date = 04/15/2006

Thanks in advance!

I cannot see link between Payment date and next payment date and payment date you mentioned is before date of purchase. how is this?
can you explain your problem a bit more and tell that how do you calculate next payment date?

**daddylonglegs** · 03-10-2007, 10:16 AM

Assuming B2 will always be earlier than C2 try this formula in D2

=EDATE(B2,CEILING(DATEDIF(B2,A2,"m")+1,12/C2))

format as date

note:EDATE requires Analysis ToolPak add-in, to install use Tools > add-ins and tick "Analysis ToolPak" box