Hi!
I want a field to show one of three values (Active, Expires in a week or Expired) depending on if the field “Startdatum” is after today, within a week or before today. How do I solve this?
Thanks!
Solved
Value based on relation to date
Best answer by Kamille_Parks11
You need three nested IF() statements: one to check if the date field is filled in, one to test if its expired, and another to differentiate between those expiring within a week or sometime in the future.
IF(
{Startdatum},
IF(
{Startdatum} < TODAY(),
"Expired",
IF(
DATETIME_DIFF({Startdatum}, TODAY(), "days") <= 7,
"Expires in a week",
"Active"
)
),
"No date"
)
+27You need three nested IF() statements: one to check if the date field is filled in, one to test if its expired, and another to differentiate between those expiring within a week or sometime in the future.
IF(
{Startdatum},
IF(
{Startdatum} < TODAY(),
"Expired",
IF(
DATETIME_DIFF({Startdatum}, TODAY(), "days") <= 7,
"Expires in a week",
"Active"
)
),
"No date"
)
You need three nested IF() statements: one to check if the date field is filled in, one to test if its expired, and another to differentiate between those expiring within a week or sometime in the future.
IF(
{Startdatum},
IF(
{Startdatum} < TODAY(),
"Expired",
IF(
DATETIME_DIFF({Startdatum}, TODAY(), "days") <= 7,
"Expires in a week",
"Active"
)
),
"No date"
)
Worked like a charm! Thanks alot :grinning:
You need three nested IF() statements: one to check if the date field is filled in, one to test if its expired, and another to differentiate between those expiring within a week or sometime in the future.
IF(
{Startdatum},
IF(
{Startdatum} < TODAY(),
"Expired",
IF(
DATETIME_DIFF({Startdatum}, TODAY(), "days") <= 7,
"Expires in a week",
"Active"
)
),
"No date"
)
Hi again! Can I have it so it doesn’t say “#ERROR” when “Startdatum” is empty?
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.