Configuring DateAdd formula with multiple conditions

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!

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
[/quote],
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)
)
1 Like

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.

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

That worked! Thank you

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.