How do I create a formula that fills in dates for the total # of payment dates?

Cheyenne

I am creating a payment template for a payback agreement. There is no interest rates. I have it set up where they enter in the payment amount they can pay towards their arrears, select if their payments are going to be weekly, bi-weekly or monthly. I have a column that gives me the total number of payments needed for the specific payment amount. I have payment dates that populate from a formula depending on if they choose Weekly, Bi-Weekly, or Monthly.

What I am trying to do is have the Payment Date populate only in the amount of rows that the number of payments is. So if there are 8 payments, the equation only fills 8 rows. Or if there are 15 payments, it automatically fills in 15 payment date rows.

Is that possible? I want to keep the last 4 rows locked in the template so the employees dont mess with the equation but I am worried they wont be able to copy the equation down if they are locked.

Paul Newcome

You would need a helper column (called "Payment Number" in this example) that can be hidden or modified with conditional formatting to hide the value(s).

You would manually enter the numbers 1 through whatever to accommodate the maximum number of payments (and add a little buffer to be safe). So if the maximum number of payments you think you'll need is 50, then I would build in 60 just to be on the safe side.

From there we would use something like this to generate your dates:

=IF([Number Of Payments]@row <> "", [Payment Start Date]@row, IF([Number Of Payments]$1<= [Payment Number]@row, IF([Payment Frequency]@row = "Weekly", [Payment Start Date]@row + (7 * ([Payment Number]@row - 1)), IF([Payment Frequency]@row = "Bi-Weekly", [Payment Start Date]@row + (14 * ([Payment Number]@row - 1)), IF([Payment Frequency]@row = "Monthly", IFERROR(DATE(YEAR([Payment Start Date]$1) + ROUNDDOWN((MONTH([Payment Start Date]$1) + ([Payment Number]@row - 1)) / 12, 0) + IF(IF(MOD(MONTH([Payment Start Date]$1) + ([Payment Number]@row - 1), 12) = 0, 12, MOD(MONTH([Payment Start Date]$1) + ([Payment Number]@row - 1), 12)) = 12, -1) - IF(AND(ABS(([Payment Number]@row - 1)) - MONTH([Payment Start Date]$1) <> 12, ([Payment Number]@row - 1) < 0, ABS(([Payment Number]@row - 1)) > MONTH([Payment Start Date]$1)), 1, 0), IF(MOD(MONTH([Payment Start Date]$1) + ([Payment Number]@row - 1), 12) = 0, 12, MOD(MONTH([Payment Start Date]$1) + ([Payment Number]@row - 1), 12)), 1), DATE(IF(MONTH([Payment Start Date]$1) - ABS(([Payment Number]@row - 1)) < 1, YEAR([Payment Start Date]$1) - 1, YEAR([Payment Start Date]$1)), IF(MONTH([Payment Start Date]$1) - ABS(([Payment Number]@row - 1)) < 1, MONTH([Payment Start Date]$1) + (12 - ABS(([Payment Number]@row - 1))), MONTH([Payment Start Date]$1) - ABS(([Payment Number]@row - 1))), 1)))))))