Configuring DateAdd formula with multiple conditions

[Aysia_Saylor]

Hello,

I have 1 single select field called “Status”.

This Status field has the following options:

• Awaiting Information (Outreach 1)
• Awaiting Information (Outreach 2)
• Awaiting Information (Outreach 3)
• On Hold

Each of these options has its own date field connected to an automation that date-stamps when the option is selected in Status.

• Outreach 1 Date
• Outreach 2 Date
• Outreach 3 Date
• On Hold Date

I need 1 formula that returns a date with the following considered:

• If Status = Awaiting Information (Outreach 1), add “2” business days to Outreach 1 Date.
• If Status = Awaiting Information (Outreach 2), add “2” business days to Outreach 2 Date.
• If Status = Awaiting Information (Outreach 3), add “3” business days to Outreach 3 Date.
• If Status = On Hold, add “30” business days to On Hold Date.

This is what I’ve tried so far with the first 3 dates conditions:

IF(Status=“Awaiting Response (Outreach 1)”,DATEADD({Outreach 1 Date},2,‘days’)) & IF(Status=“Awaiting Response (Outreach 2)”,DATEADD({Outreach 2 Date},2,‘days’)) & IF(Status=“Awaiting Response (Final Outreach)”,DATEADD({Outreach 3 Date},3,‘days’))

But it returns a string value (e.g., “2022-01-06T00:00:00.000Z”).

What is the easiest way to configure this formula with all of the necessary conditions while also returning the date in a universal format (e.g., MM-DD-YYYY) and using business days in the dateadd formula?

This would be used to connect to an automation that sends us a message when this field is today’s date so we know when to conduct an outreach.

Thanks!

[Kamille_Parks · Accepted solution]

You are concatenating multiple IF()s together instead of using nested IF()s. This is forcing your date to output as a string. I would recommend a setup using a SWITCH() statement to ensure only one output. Nested IF()s can work too, but in this case the formula would be repetitive since you’re analyzing the result of {Status} several times.

[quote=“Aysia_Saylor, post:1, topic:45477”]
using business days in the dateadd formula
,
The function you’re looking for is WORKDAY(), not DATEADD(). Full list of formula functions for Airtable

SWITCH(
{Status},
"Awaiting Response (Outreach 1)", DATEADD({Outreach 1 Date}, 2, "days"),
"Awaiting Response (Outreach 2)", DATEADD({Outreach 2 Date}, 2, "days"),
"Awaiting Response (Outreach 3)", DATEADD({Outreach 3 Date}, 3, "days"),
"On Hold", WORKDAY({On Hold Date}, 30)
)

[Aysia_Saylor]

Hi Kamille,

Thanks for your assistance. This makes sense, but when I tried to copy/paste the formula into the field it’s not accepting it. I double-checked to make sure the names of the fields and single select options were still correct. This is the message that comes up:

“Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”

I also tried replacing the double quotes (") with singles (’) just in case.

[Kamille_Parks]

Whoops, there needs to be a comma after {Status}

[Aysia_Saylor]

That worked! Thank you