This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- $100 Reward! Calculating upcoming billing date

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1107
19

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 05:02 PM

Reply

19 Replies 19

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 01:37 PM

Hi Per. I’m not sure that I get your examples. In the “Monthly” case, you want to add 3 years and 9 months to the {Start Date}? In the “Semi-annually” case, you want to add 1 year and 6 months? I’m pretty sure that’s not what you mean, so I’ll answer the question that I think you’re asking. If I misunderstood, you can still use my answer, just change the SWITCH values to something else.

I just answered a question very similar to this one today. Put this formula in your {Next Billing} field.

```
DATEADD({Start Date},
SWITCH({Billing},
'Monthly', 1,
'Quarterly', 3,
'Semi-annually', 6,
'Annually', 12),
'months'
)
```

You can use an automation from here if you want to update {Billing} at the end/start of a cycle. Good luck.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 01:45 PM

Hi!

It’s not that I want to add a specific amount of months in each example, I want the formula to calculate the next upcoming (future) billing date. Your formula gives me dates in the *past*. `Next Billing`

should always be sometime in the *future* (or today).

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 01:52 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 01:57 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 02:05 PM

Hmm…let me think…give this a try maybe?

```
DATEADD(
DATETIME_PARSE(MONTH({Start Date}) & '/' & DAY({Start Date}) & '/' & YEAR(NOW()),
'I'),
SWITCH({Billing},
'Monthly', 1,
'Quarterly', 3,
'Semi-annually', 6,
'Annually', 12),
'months'
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 02:09 PM

That gives me **#ERROR!** :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 02:17 PM

Replace the ‘I’ in the DATETIME_PARSE with ‘MM/DD/YYYY’. Let me know.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 02:20 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 02:21 PM

Nevermind. I see the problem. I’ll think about it.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 03:04 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 03:04 PM

Thanks for trying! :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 03:27 PM

Anyone got any ideas? I’ll PayPal $100 to whomever solves this problem.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 03:38 PM

That’s **EXACTLY** what I want! Are there any downsides of the formula being so long?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 03:54 PM

`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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 04:21 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 04:24 PM

Lol :slightly_smiling_face: PM me with your PayPal account and I’ll send you the money.

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2021 05:02 PM

Thank you! This was an interesting formula to build.

Reply