$100 Reward! Calculating upcoming billing date

[Per_Carlen]

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!

Airtable

Next Billing Problem - Airtable

Explore the "Next Billing Problem" view on Airtable.

[augmented]

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.

[Per_Carlen]

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!

[augmented]

Do you mean that you want to give the formula a {Start Date} that may be years in the past, set the {Billing} to “monthly” for example, and then have the {Next Billing} be the same day of month in the next month of this year? Just an example.

[Per_Carlen]

Yes, that’s exactly what I want. I just want to know “when is the next time I should send a bill to this customer based on it’s start date and billing interval?”.

[augmented]

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'
)

[Per_Carlen]

augmented:

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

That gives me #ERROR! :slightly_smiling_face:

[augmented]

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

[Per_Carlen]

augmented:

MM/DD/YYYY

That got rid of the error. Some dates are correct, but some dates are in the past (which is wrong). I’ll PayPal you $100 if you solve this one.

[augmented]

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