Skip to main content

I’m trying to calculate the next due date of something based on whether the occurrence is monthly or annually.


I’ve got this:


IF(

{Past?}=“0”,

{Start Date},

DATEADD({Start Date},1,‘years’

)

)


And I’ve got a formula that calculates the 1/0 for the {Past?} field.


That seems to work so far in terms of figuring out the next due date, but only on an annual recurrene.


But there’s another dropdown {Recurrence} that is either Monthly or Annually.


What I’d like to do is say, “If recurrence = “Monthly”, add 1 month…if “Annually” add 1 year.”


I’m sure it’s some kind of IF/AND/OR but I can’t seem to crack the logic on that.

Hi there @Dan_Smock, welcome to the community!


Try this:


IF({Start Date}, IF(IS_BEFORE({Start Date}, TODAY()), DATEADD({Start Date}, 1, SWITCH({Recurrence}, "Monthly", 'month', "Annually", 'year'))))

If {Start Date} is before today, this formula will add either one month or one year to {Start Date} based on the value of the field {Recurrence}.


The first statement, IF({Start Date},… simply checks if there is a value in the field before starting the formula. This is to eliminate any errors if {Start Date} is empty 🙂


Hope this helps!

Hi there @Dan_Smock, welcome to the community!


Try this:


IF({Start Date}, IF(IS_BEFORE({Start Date}, TODAY()), DATEADD({Start Date}, 1, SWITCH({Recurrence}, "Monthly", 'month', "Annually", 'year'))))

If {Start Date} is before today, this formula will add either one month or one year to {Start Date} based on the value of the field {Recurrence}.


The first statement, IF({Start Date},… simply checks if there is a value in the field before starting the formula. This is to eliminate any errors if {Start Date} is empty 🙂


Hope this helps!


Yup! Sure does! Thanks! Never have used “Switch” before, so that’s pretty awesome.

Reply


Terms & Conditions