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?
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
