Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Jul 27, 2022 02:05 AM
What I need:
For a field to update automatically with a status change if there is 21 days remaining before the clients renewal date.
For example, if the next renewal date is 21 days away I would want a “Status of Renewal” field to say ‘Close To Renewal’, but I would also want to know if the member is still ‘Active’ or if they are past the ‘Next Renewal’ date then it has to say ‘Needs Renewal’.
Jul 27, 2022 03:24 AM
Hi
I will give this to you as a two part formula for ease and clarity but obviously can be nested one inside the other
so to get the days between now and renewal - Calculation of days to renewal is defined as
DATETIME_DIFF(Notes,TODAY(),‘days’)
then you can have a second formula
IF(Calculation<0,“Needs Renewal”,IF(Calculation<21,“Close to Renewal”,“OK”))
This obviously gives your field as a formula - if you want it to be anything else (e.g. single select field) - then you will need to use automations based on the results of those formulae and potentially views to trigger