Skip to main content

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

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

Reply


Terms & Conditions