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"
)
)
)
)
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!!
t5:
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!!
t5:
Working from outside in:
- The first IF() makes sure the End Date field isn’t empty. This prevents the field from showing errors
- The second IF() makes sure that the End Date hasn’t happened yet. If its already occurred then you will get a negative number for “the difference between days”
- The third and 4th IF()s are straightforward. They are presented in this order because checking for if a number is less than 60 first may include numbers that are also less than 30. “A number less than 30 must be less than 60”, so logically we want to know “If it is not less than 30, is it less than 60?”