Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Configuring DateAdd formula with multiple conditions

Topic Labels: Formulas
Solved
Jump to Solution
2344 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Aysia_Saylor
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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

See Solution in Thread

4 Replies 4
Kamille_Parks
16 - Uranus
16 - Uranus

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

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