How do I create a formula that fills in dates for the total # of payment dates?
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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!