Need a Formula to Auto populate dates

AudreyKay

AudreyKay

New member
Joined
Nov 4, 2015
Messages
4
Reaction score
0
Points
0
Okay, Trying to get a table to populate. I'm not sure if there's even a way, and if there is, it's probably really complicated.

In cell B16 I have a drop down list that you can select a payment frequency of bi weekly or weekly. I want to make it so when, for example, they select weekly, the table on the right will populate with the weekly dates for each payment, all the way down till the last week of payment.

I also want it to where it automatically figures out the end date in B19 given the information it receives from B16 and B17.


Please help!
 

Attachments

  • PayAdvance Template.xlsx
    16.4 KB · Views: 30
Is the first payment on the start date, and what day(s) will you pay with the bi-weekly option?
 
Try this, assuming Bi-Weekly is Thursday and Sunday:
 

Attachments

  • BiWeeklyDates.xlsx
    13.9 KB · Views: 16
using your posted workbook...Maybe something like this:
Code: 
I5: =MAX($C$14+$C$15*-[@[Payment '#]],0)
H5: =-MAX(MIN($C$14-[@[Payment '#]]*$C$15,$C$15),0)
G5: =IF(ABS([@[Payment Amount]])>0,$C$18+([@[Payment '#]]-1)*INDEX($C$33:$C$34,MATCH($C$16,Frequency,0)),"")

Expand your table rows to a max number of expected payments.
Copy those formulas down to the bottom of the table.

If the payments do not divide evenly into the principal, the last payment will be decreased to zero out the balance.

Is that something you can work with?
 
You must log in or register to reply here.
Back
Top