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!