Membership Status Formula

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!

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")))))