Nested if statements

[Madalina_Belciu]

Hi!

I am working on a content management dashboard. I have a single choice field with multiple types of assets, and I have an expiration date field with expiration dates for each asset. I want to create a formula field named Needs Review, that would return different values. For example:

If asset type is blog or newsletter, then Needs Review field should show Yes when expiration date is within 60 days. If expiration date is later than 60 days from today it should show No.
If asset type is video or webinar, then Needs Review field should show Yes when expiration date is within 90 days. If expiration date is later than 90 days from today it should show No.
And so on with a few other types of assets.

I also want to add some emojis for the answers in the Needs Review field.

I tried writing the formula using IF, AND and DATETIME_DIFF() but I can’t find a way to format it correctly. Do you have some advise on what’s the best way to write this formula?

Thank you!

[Zack_S]

Hi @Madalina.Belciu,

Sometimes it’s easier to break things down into an extra field or two.

I would setup an extra field called something like ‘review range’ that holds your 60 and 90 days intervals and use a SWITCH formula. See below.

SWITCH(Status, 
"Blog", 60, 
"Newsletter", 60,
"Video", 90
)

Then your ‘Needs Review’ field can contain the following formula.

IF(DATETIME_DIFF({Due Date}, TODAY(), 'days')<={Review Range (days)}, "✅ Yes", "⛔ No")

Hopefully, this is what you are looking for.