Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 25, 2021 08:12 AM
I have three fields like this:
| Start Date | Billing | Next Billing |
In the Start Date
field, there’s a past date
In the Billing
field, there’s a dropdown list with the values “Monthly”, “Quarterly”, “Semi-annually” and “Annually”
In the Next Billing
field, I want a formula that calculates next billing date based on the values in the two previous fields. Next Billing
should always be a upcoming date (or today).
Examples:
If Start Date
is “2018-01-01” and Billing
is “Monthly”, I want Next Billing
to say “2021-09-01”
If Start Date
is “2020-06-18” and Billing
is “Semi-annually”, I want Next Billing
to say “2021-12-18”
If Start Date
is “2016-10-24” and Billing
is “Annually”, I want Next Billing
to say “2021-10-24”
I have tried to do this myself but I can’t work out how to do it. Can someone please help?
Thanks!
Solved! Go to Solution.
Aug 25, 2021 03:04 PM
Thanks Per. I see the issue more clearly now. I’m not sure I have the skill to solve this in a formula field. Good luck!
Aug 25, 2021 03:04 PM
Thanks for trying! :slightly_smiling_face:
Aug 25, 2021 03:27 PM
Anyone got any ideas? I’ll PayPal $100 to whomever solves this problem.
Aug 25, 2021 03:36 PM
Is this what you want?
Would you like to provide any more test data?
By the way, this formula is 62 lines long.
Aug 25, 2021 03:38 PM
That’s EXACTLY what I want! Are there any downsides of the formula being so long?
Aug 25, 2021 03:48 PM
Sometimes a formula has to be long in order to include all the logic.
One down side is that the longer the formula, the longer it takes Airtable to recalculate. On the other hand, Airtable will eventually complete the recalculations. While this formula is on the longer side, it is far from reaching the limits of Airtable’s capabilities, and it still recalculates reasonably quickly.
Another down side of long formulas is that they can be difficult to maintain, as there is no built-in way of including comments in a formula. This is why it is very important to have a quality formula to start with that covers all of your potential edge cases. If there is a small logic error in the formula that you don’t discover until after the formula is in production, it can be very difficult to root out the problem. This is also why I ask if you want to test any additional dates before purchasing the formula, while the algorithm is fresh in my mind. (I do write my formulas to make them easy to read, but even so it can be difficult to retrace the logic in these formulas after the fact due to the lack of comments.)
Mostly, I list the number of lines in the formula to indicate that I do not consider this to be a trivial formula that can be solved in only a few lines.
Aug 25, 2021 03:54 PM
Thanks for clarifying! The only problem I can see in your test is the record with “Quarterly” as billing interval. Next Billing
should be 2021-09-28, as I will bill this customer every quarter starting from March 28th. I.e March 28th, June 28th, September 28th and December 28th each year. Let me know if anything is unclear.
Aug 25, 2021 04:21 PM
Thanks for the catch. This is what I mean about the importance of testing cases. I got mixed up between a quarter being 3 months versus 4 months. Lol. There are 4 quarters in a year, but each quarter should be only 3 months, not 4 months long.
Aug 25, 2021 04:24 PM
Lol :slightly_smiling_face: PM me with your PayPal account and I’ll send you the money.
Aug 25, 2021 05:02 PM
Thank you! This was an interesting formula to build.