Skip to main content

Monthly renewal

  • May 17, 2019
  • 2 replies
  • 25 views
D
Forum|alt.badge.img+5

Hello!

I’m using airtable to keep track of memberships. I have a column with “Member since” date and I want every month after that we need to make a new package for them. Once that that has passed I want it to show the next month etc.

I’m using this formula to calculate “next package” date:

IF(DATETIME_DIFF(TODAY(),{Member since}, ‘months’)>1,DATEADD({Member since},(ROUNDDOWN(DATETIME_DIFF(TODAY(),{Member since},‘months’)/1,0)+1)*1,‘months’),DATEADD({Member since},1,‘months’))

It is working well except for the first renewal.
If they joined more than 2 months ago the formula works.
But the members that joined between 1 & 2 months ago are showing a next package date in the past…

Anyone that can help with this?

2 replies

JonathanBowen
Forum|alt.badge.img+18

Hi @Dirk_Jan_de_Wit - the problem lies in the first part of the IF() formula:

DATETIME_DIFF(TODAY(),{Member since}, 'months') > 1

I created a formula field showing the output of this formula, i.e. of this:

DATETIME_DIFF(TODAY(),{Member since}, 'months')

And the results are this:

The formula is outputting full months only, so right now it is the 17/05 for me so the formula for 17/03 shows “2”, i.e. 2 full months. For the 18/03, it isn’t yet 2 full months, just 1 and most of the 2nd, so only shows the result 1.

So, going back to the first part of the IF:

DATETIME_DIFF(TODAY(),{Member since}, 'months') > 1 for 18/03 this won’t be satisfied and the last part of the IF formula will apply, i.e. just adding a single month.

What I think you need to do is make the formula:

DATETIME_DIFF(TODAY(),{Member since}, 'months') >= 1

which will then give you this:

Is that what you are expecting to see?

JB

D
A
Justin_Barrett
D
Forum|alt.badge.img+5
  • Dirk_Jan_de_Wit
  • Author
  • Participating Frequently
  • May 24, 2019

Hi @Dirk_Jan_de_Wit - the problem lies in the first part of the IF() formula:

DATETIME_DIFF(TODAY(),{Member since}, 'months') > 1

I created a formula field showing the output of this formula, i.e. of this:

DATETIME_DIFF(TODAY(),{Member since}, 'months')

And the results are this:

The formula is outputting full months only, so right now it is the 17/05 for me so the formula for 17/03 shows “2”, i.e. 2 full months. For the 18/03, it isn’t yet 2 full months, just 1 and most of the 2nd, so only shows the result 1.

So, going back to the first part of the IF:

DATETIME_DIFF(TODAY(),{Member since}, 'months') > 1 for 18/03 this won’t be satisfied and the last part of the IF formula will apply, i.e. just adding a single month.

What I think you need to do is make the formula:

DATETIME_DIFF(TODAY(),{Member since}, 'months') >= 1

which will then give you this:

Is that what you are expecting to see?

JB


That’s great! Thanks @JonathanBowen

Terms & ConditionsAccessibility statement