# \$100 Reward! Calculating upcoming billing date

Topic Labels: Formulas
Solved
4384 19
cancel
Showing results for
Did you mean:
7 - App Architect

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!

1 Solution

Accepted Solutions
18 - Pluto

Thank you! This was an interesting formula to build.

19 Replies 19
10 - Mercury

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.

7 - App Architect

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!

10 - Mercury

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.

7 - App Architect

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?”.

10 - Mercury

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'
)
``````
7 - App Architect

That gives me #ERROR! :slightly_smiling_face:

10 - Mercury

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

7 - App Architect

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.

10 - Mercury

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