If Condition on Membership Date

[Agor]

Hello AirTable Community!

We are currently at a stand-still on our sheet. What we were are currently doing is the folllowing:

• Check If membership has a member.
  If successful, proceed to next condition, if not ,label "No Member"

• If Membership has a member, check if the member has no passport passes
  If successful, proceed to next condition, if not ,label "Passport Holder"

• If successful, check if member currently has passes left
  If successful, proceed to next condition, if not, label "For Renewal"

• If successful, check if renewal date is less than the date today
  If successful, label member as “Active”, if not, label member as "For renewal"

What I’m currently facing is that if the member has passed through the condition for the date checking, and the renewal date is lesser than the date today. It sill labels the member as “Active”, and not "For Renewal"

Any insight on our current predicament is welcomed.

Here’s a link to our sheet.

Here’s the formula for the conditions

IF({Member Details} !="", 
	IF({Passport Passes} ='', 
		
		IF(
			{Membership Passes Left} > 0, 
				IF(
					DATETIME_FORMAT({Renewal Date}, 'MM/DD/YYYY') <= DATETIME_FORMAT(TODAY(), 'MM/DD/YYYY') &
					DATETIME_FORMAT({Renewal Date}, 'YYYY') <= DATETIME_FORMAT(TODAY(), 'YYYY'),
					"Active",
					"For Renewal"),
				"For Renewal"
		),

	"Passport Holder"),	
"No Member")

[Justin_Pante]

Hi,

I took a look at the logic for the date comparison. The core of your issue actually lies around how you’re generating the renewal dates. The DATETIME_FORMAT produces a string so your logic is actually comparing to string values not date values.

By removing the formatting from the formula for the Renewal Date field then when the comparison in your Membership Status field is run it will evaluate the two dates correctly (see below)

Renewal Date field formula

IF({Member Details} !="", 
	IF({Membership Date} !="", 
		DATEADD({Membership Date}, SUM({Updated Validity}), 'days'), 
		"No Start Date"),
"No Member")

Granted the format is not as pretty but maybe you could play around with your columns to have a hidden field or alternatively, remove the String notes from the Renewal Date column and use the Formatting option on the field (this should retain the date field information so it can still be evaluated).

Also once this is done you can simplify the logic for the Membership Date to this.

Membership Status field formula

IF({Member Details} !="", 
	IF({Passport Passes} ='', 
		IF(
			{Membership Passes Left} > 0, 
				IF(({Renewal Date}<TODAY()),'Needs Renewal','Active'),
				"For Renewal"
		),

	"Passport Holder"),	
"No Member")

Hope this helps!

[Agor]

Hey Justin!

Thanks a lot. That clarified a lot of things.
We’re happy to say that the update you proposed on the formula is working beautifully.

We removed the string output for the Renewal Date field. And we’re currently moving forward to our next formula creation!

Thanks again!

[Justin_Pante]

Nice! Glad to help :slightly_smiling_face: