Help

Calculate anniversary date using IF

Topic Labels: Formulas
1571 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Dan_Smock
4 - Data Explorer
4 - Data Explorer

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.

2 Replies 2
AlliAlosa
10 - Mercury
10 - Mercury

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 :slightly_smiling_face:

Hope this helps!

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