Re: Set due date based on Name and Date
@Jane_Hardy You are almost there! I only changed 2 things:
- Your
DATEADDfunctions all have a starting parentheses that is not matched. I simply removed it. - Your quotes are malformed. This can be difficult sometimes to tell, but there are different kinds of quotes: straight quotes and smart quotes. Smart quotes are great for human readable text, but not machine readable text. Generally, straight quotes are automatically inserted when typing on a keyboard, but some applications like Word auto-generate smart quotes. See this article for a better explanation, but TLDR: all I did was convert your smart quotes to straight quotes.
Note: Discourse does automatically change to smart quotes, so it could be that you have straight quotes already. If this is the case, make sure to place formulas in-between backticks (`) next time you comment on the Community Forum so that it keeps text as-is.
Here is a complete function:
IF(
Name="Funnel and Ad Copy",
DATEADD({Checklist Returned (from Elite Details)},5,"days"),
IF(
Name="Funnel Build",
DATEADD({Checklist Returned (from Elite Details)},10,"days"),
IF(
Name="Creative",
DATEADD({Checklist Returned (from Elite Details)},10,"days"),
IF(
Name="Integrations",
DATEADD({Checklist Returned (from Elite Details)},14,"days")
)
)
)
)
You can make this slightly better by doing a little more error checking on the date. If your {Checklist Returned (from Elite Details)} is not a date, your function will error. Instead, wrap the whole thing in another IF function to check for this:
IF(
{Checklist Returned (from Elite Details)},
IF(
Name="Funnel and Ad Copy",
DATEADD({Checklist Returned (from Elite Details)},5,"days"),
IF(
Name="Funnel Build",
DATEADD({Checklist Returned (from Elite Details)},10,"days"),
IF(
Name="Creative",
DATEADD({Checklist Returned (from Elite Details)},10,"days"),
IF(
Name="Integrations",
DATEADD({Checklist Returned (from Elite Details)},14,"days")
)
)
)
)
)
While this will work, I think it can be better. Currently, adding new names becomes increasing difficult, as you need to add a new nested layer. Instead, Airtable has implemented a function called SWITCH that may be better than nested IF statements. A switch implementation would look like this:
IF(
{Checklist Returned (from Elite Details)},
DATEADD(
{Checklist Returned (from Elite Details)},
SWITCH(
Name,
"Funnel and Ad Copy", 5,
"Funnel Build", 10,
"Creative", 10,
"Integrations", 14,
2
),
"days"
)
)
This is not only easier to read, it is also easier to edit. There is less nesting, and you only have to add the number rather than the entire DATEADD function with the repeated {Checklist Returned (from Elite Details)} field. Also, note that a default number (in this case 2) can be added to whatever number you would like the default to be if the Name does not match. Here is an example of what the outputs will look like:
As can be seen in the screenshot, it easily adds the appropriate amount if the day matches, and if the day does not match, it adds the default (2). If there is no date, it stays blank.
Note: You can add an else statement to the
IFstatement if you like to print “Please enter a date in theChecklist Returned (from Elite Details)field”, but do note that this means the formula outputs both a date and a string. If your formula outputs more than 1 type, you will not be able to change the formatting of your formula field.
I hope this helps!
