Oct 09, 2019 01:20 AM
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}:
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!
Nov 02, 2019 06:34 AM
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")))))