Membership Status Formula

[Jessica_Pena]

Hi there,

I am working on a membership base and trying to create a formula field that will generate one of the following statuses depending on the membership expiration date {Expires}:

• Active (Any time 31-365 days before Expires)
• Time to Renew (30 days before Expires)
• About to Expire (15 days before Expires)
• Expired (up to 90 days after Expires)
• Lapsed (anything after 90 days after Expires)

A couple of things to know: I have two tables in my base, a Transactions table and a Members & Donors table. The ‘Status’ field will live in the Members & Donors table and uses the ‘Expires’ field, which is a formula field that adds one year to the ‘Last Donation Date’ field, which in turn is a MAX(values) Rollup field to get the most recent transaction for each donor from the Transactions table.

I have tried my hand at a formula following the most similar circumstances I could find in the Airtable community to get the different statuses with no luck yet.

Any help on this is greatly appreciated!

[Justin_Barrett]

Sorry to see that this has gone unanswered for so long, @Jessica_Pena. :frowning: Setting this up only takes a couple of fields. First is one I’m calling {Diff}, which calculates the difference between the {Expires} date and today:

IF(Expires, DATETIME_DIFF(TODAY(), Expires, "days"))

The other is {Status}, which contains the following formula:

IF(Expires, IF(Diff > 90, "Lapsed", IF(Diff >= 0, "Expired", IF(Diff > -16, "About to Expire", IF(Diff > -31, "Time to Renew", "Active")))))