Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 08, 2022 03:33 PM
Hello, I am horrible at formulas and it’s really becoming a frustration as I continue to learn Airtable. I am wanting to create a status in the formula field to then automate an email trigger.
Seems simple enough but I am a struggle bus.
What formula do I need to enter? If we are 60-days out from the contract ending, update the status to 60-days, and if we a 30-days from the contract ending, update the status to 30-days.
Start with IF(AND(, correct?
IF(AND({Contract End Date} …blank stare…, “60-days”), repeat blank stare, “30-days”)
Help :weary:
Solved! Go to Solution.
Feb 08, 2022 04:40 PM
See the formula below which should do as you asked using 4 nested IFs
IF(
{Contract End Date},
IF(
{Contract End Date} > TODAY(),
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30,
"30 days",
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60,
"60 days",
"more than 60 days"
)
),
"contract end date passed"
),
"no contract end date"
)
If you don’t want any text to display if its neither within 30 days nor 60 days then simplify that formula to:
IF(
{Contract End Date},
IF(
{Contract End Date} > TODAY(),
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30,
"30 days",
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60,
"60 days"
)
)
)
)
Feb 08, 2022 04:40 PM
See the formula below which should do as you asked using 4 nested IFs
IF(
{Contract End Date},
IF(
{Contract End Date} > TODAY(),
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30,
"30 days",
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60,
"60 days",
"more than 60 days"
)
),
"contract end date passed"
),
"no contract end date"
)
If you don’t want any text to display if its neither within 30 days nor 60 days then simplify that formula to:
IF(
{Contract End Date},
IF(
{Contract End Date} > TODAY(),
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30,
"30 days",
IF(
DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60,
"60 days"
)
)
)
)
Feb 10, 2022 07:19 PM
You just saved me! This makes zero sense right now, but it will over the next few months as I keep using Airtable. Thank you so much! I am beyond appreciative of your help!! :raised_hands:t5:
Feb 11, 2022 09:56 AM
Working from outside in: