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

Cheyenne
Cheyenne ✭✭
edited 11/07/22 in Formulas and Functions

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.


Answers

  • Paul Newcome
    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)))))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!