Help with IF nest formula

[Emmanuel_Saint-]

I am trying to return “Late” when one or several function fields have a date that’s before today AND an attachment field is empty. I have tried the following formula but it’s not working. What am I doing wrong?

IF(
AND(
IS_BEFORE(
{Due Date Sponsored Article},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”)
IF(
AND(
IS_BEFORE(
{Due Date Sponsored Article},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”)
IF(
AND(
IS_BEFORE(
{Due Date Sponsored Agenda},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”)
IF(
AND(
IS_BEFORE(
{Due Date NL Banners},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”)
IF(
AND(
IS_BEFORE(
{Due Date EMail Blast},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”)
IF(
AND(
IS_BEFORE(
{Due Date Website Banners},TODAY()),
{Assets}="",
“LATE”,
“Upcoming”))))))

[Elias_Gomez_Sai]

Your formula is not well formatted:

• “Late” and “Upcoming” should be outside the AND. Image: https://imgur.com/a/mmYvrCc
• To make a nested IF, the child IFs should be inside another IF, you can’t put several IFs in serie (unless you want to concatenate the results with &😞 https://support.airtable.com/hc/en-us/articles/221564887-Nested-IF-formulas
• The most important, you only need one IF:

IF(
	AND(
		OR(
			Date Check 1,
			Date Check 2,
			Date Check 2,
			...
		),
		{Assets}=""
	),
	'Late',
	'Upcoming'
)

Change the “Date Checks” to a proper functions, I didn’t want to write all of them :grinning_face_with_smiling_eyes:

[Emmanuel_Saint-]

Thank you Elias, exactly what I needed!

[Emmanuel_Saint-]

I might have been too quick to respond actually. There is still something I am doing wrong. Here is the code I am trying:

IF(
AND(
OR(
IS_BEFORE(
{Due Date Sponsored Article},TODAY()),
IS_BEFORE(
{Due Date Sponsored Agenda},TODAY()),
IS_BEFORE(
{Due Date NL Banners},TODAY()),
IS_BEFORE(
{Due Date EMail Blast},TODAY()),
IS_BEFORE(
{Due Date Website Banners},TODAY()),
{Assets}=""
)),
“Late”,
“”
)

Could it be due to the fact that some of the date fields are empty? If that’s the case, is there a way around?

[Elias_Gomez_Sai]

Your {Assets}="" is inside the OR() instead of the AND():

What error are you getting?

[David_Skinner]

You are using two different types of quotation mark in that formula. I don’t think the curly ones around the word late are compatible with airtable formulae.

This is often an Apple problem because their keyboards default to using the curly quotation marks.

[Emmanuel_Saint-]

Thank you Elias
I am getting Error returned whenever one of the dates is blank (which happens most of the times). When all date fields have a date, it works fine.

[Elias_Gomez_Sai]

I think is because of the IS_BEFORE, you can check that. Maybe you have to wrap all of them with IF({Date}, [formula]).

[Emmanuel_Saint-]

I am trying to use an IS_Before check date with AND, IF and OR. It’s working but only when all date fields have a date. If a field is blank, it returns ERROR.
Then I tried to wrap each IS_BEFORE check in an IF(). No more errors, but the formula doesn’t return anything at all.
Anyone seeing what I am doing wrong?

IF(
AND(
OR(
IF({Due Date Sponsored Article},
IS_BEFORE(
{Due Date Sponsored Article},TODAY()),
IF({Due Date Sponsored Agenda},
IS_BEFORE(
{Due Date Sponsored Agenda},TODAY()),
IF({Due Date NL Banners},
IS_BEFORE(
{Due Date NL Banners},TODAY()),
IF({Due Date EMail Blast},
IS_BEFORE(
{Due Date EMail Blast},TODAY()),
IF({Due Date Website Banners},
IS_BEFORE(
{Due Date Website Banners},TODAY())))))),
{Assets}=""
),
“Late”,
“”
))

[Elias_Gomez_Sai]

Why you don’t reply to the original post?

You have nested the IFs:

Close them before starting the new one.