Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Monthly renewal

Topic Labels: Formulas
952 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

Screenshot 2019-05-16 at 11.05.37.png

2 Replies 2

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:

Screenshot 2019-05-17 at 23.24.49.png

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:

Screenshot 2019-05-17 at 23.28.41.png

Is that what you are expecting to see?

JB