If you have an event, task, bill or subscription that happens on a weekly, monthly or yearly recurring cycle, here is a formula that will tell you when is the next occurrence (after today).
This formula assumes that you have a “Frequency” select property, with options for “Weekly”, “Monthly” and “Yearly”, and also a “Start Date” property to show the date of the first occurrence.
Now we will create a new property for “Next Occurrence” to hold our formula.
First, we are going to check to see if the “Start Date” is in the future, and if so, we will simply display the start date in the Next Occurrence field.
if(now() < prop("Start Date"), prop("Start Date"), ...Then, for each frequency type, we will calculate the amount of time that has elapsed since the Start Date and today, then add 1 unit. For example, if the frequency type is “Weekly”:
if(prop("Frequency") == "Weekly", dateAdd(prop("Start Date"), dateBetween(now(), prop("Start Date"), "weeks") + 1, "weeks")We repeat for each of the frequency types and increment accordingly. Here is all of it put together:
if(now() < prop("Start Date"), prop("Start Date"), if(prop("Frequency") == "Weekly", dateAdd(prop("Start Date"), dateBetween(now(), prop("Start Date"), "weeks") + 1, "weeks"), if(prop("Frequency") == "Monthly", dateAdd(prop("Start Date"), dateBetween(now(), prop("Start Date"), "months") + 1, "months"), if(prop("Frequency") == "Yearly", dateAdd(prop("Start Date"), dateBetween(now(), prop("Start Date"), "years") + 1, "years"), prop("Start Date")))))
