Help with IF nest formula


#1

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”))))))


Need help with IS_BEFORE
#2

Your formula is not well formatted:

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 :smile:


#3

Thank you Elias, exactly what I needed!


#4

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?


#5

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

What error are you getting?


#6

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.


#7

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.


#8

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


#9

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”,
“”
))


#10

Why you don’t reply to the original post?

You have nested the IFs:

Close them before starting the new one.


#11

Thanks Elias. I tried that but got the same result (meaning no result).

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”,
“”
))