Hello community.
I'm wondering about comparing dates in automations. It seems this isn't a thing that airtable can do, maybe you can help me find an elegant way to set myself up.
Our company changed the date of their fiscal year (FY) and I need to calculate which FY a member joined, renewed or cancelled.
I can do it with a formula for each date, for example like this for the subscription date: (and let me know if it could be improved)
IF({Subscription date},
IF(
IS_BEFORE(
{Subscription date},
"07/01/2019"
),
DATETIME_FORMAT(
{Subscription date},
'YYYY'
),
IF(
VALUE(
DATETIME_FORMAT(
{Subscription date},'M'
)
)
>=7,
DATETIME_FORMAT(
DATEADD(
{Subscription date},
1,
'year'
),
'YYYY'
),
DATETIME_FORMAT(
{Subscription date},
'YYYY'
)
)
)
)
but I'm trying to make this a bit more user-friendly for the admin team than having three formula fields (potentially more if also tracking dates that members upgraded or downgraded.)
I had set up another table with the start and end dates of each fiscal year and was hoping for an
"if subscription date in membership table is (after start date and before end date in FY table) update subscription FY in membership table to FY table FY" sort of automation, but I can't compare dates in automations.
In this case, I doubt that further changes will be made to the fiscal year, but in other scenarios where admins might need to make changes to important dates in a central location that might affect multiple projects, is there no way of setting it up other than going into each formula field to implement central changes??
This seems like a hole and makes me feel like I'm missing something.
