DATEADD formula based on variations from two other columns

[airballer86]

I have a campaign work back schedule. I need to calculate four date fields based on the Launch Date:

• Assign Due Date
• Developer Due Date
• QA Due Date
• Approval Due Date
• Launch Date

The number of days for each due date varies based on the combination of two single-select fields:

{Channel}: Email, Push, Banner

{Send Size}: Large, Small

The logic is:

If Email (Channel) and Large (Send size) with Launch Date 6/1/23

• {Assign Due Date} = 6/1, -12 days 
• {Developer Due Date} = 6/1, -7 days
• {QA Due Date} = 6/1, -6 days
• {Approval Due Date} = 6/1, -7 days 
• {Launch Date} = 6/1

If Push (Channel) and Small (Send size) with Launch Date 6/1/23

• {Assign Due Date} = 6/1, -5 days 
• {Developer Due Date} = 6/1, -2 days
• {QA Due Date} = 6/1, -1 days
• {Approval Due Date} = DELETE (or show NA)
• {Launch Date} = 6/1

I'm new to airtable so I hope this isn't a noob question.

[FlywheelConsult]

In order to make it easier to grok, I would have individual fields for each type of date you need to calculate. You're probably going to want to use AND() and OR() statements with your IF() statements to figure out the logic you need. For example, for assign due date if the channel is email and the send size is large, your IF() statement might start like this: IF(AND({Channel}='Email',{Send Size}='Large'),DATEADD({Launch Date},-12,'days',[Here you would list alternatives or nest IF() statements])

[joshsorenson]

You can use Airtable's formula feature to calculate the date based on your specific conditions. Here's a step-by-step guide on how to create these formulas for each date field:

1. Create a new table with the following fields:

   • Channel (single select: Email, Push, Banner)
   • Send Size (single select: Large, Small)
   • Launch Date (date field)
   • Assign Due Date (formula field)
   • Developer Due Date (formula field)
   • QA Due Date (formula field)
   • Approval Due Date (formula field)

2. Click on the "Assign Due Date" field, then click "Customize field type." Choose the "Formula" field type. Enter the following formula:
   
   

IF(
   AND({Channel} = "Email", {Send Size} = "Large"),
   DATEADD({Launch Date}, -12, "days"),
   IF(
      AND({Channel} = "Push", {Send Size} = "Small"),
      DATEADD({Launch Date}, -5, "days"),
      ""
   )
)

3. Click on the "Developer Due Date" field, then click "Customize field type." Choose the "Formula" field type. Enter the following formula:

IF(
   AND({Channel} = "Email", {Send Size} = "Large"),
   DATEADD({Launch Date}, -7, "days"),
   IF(
      AND({Channel} = "Push", {Send Size} = "Small"),
      DATEADD({Launch Date}, -2, "days"),
      ""
   )
)
​

4. Click on the "QA Due Date" field, then click "Customize field type." Choose the "Formula" field type. Enter the following formula:

IF(
   AND({Channel} = "Email", {Send Size} = "Large"),
   DATEADD({Launch Date}, -6, "days"),
   IF(
      AND({Channel} = "Push", {Send Size} = "Small"),
      DATEADD({Launch Date}, -1, "days"),
      ""
   )
)
​

5. Click on the "Approval Due Date" field, then click "Customize field type." Choose the "Formula" field type. Enter the following formula:

IF(
   AND({Channel} = "Email", {Send Size} = "Large"),
   DATEADD({Launch Date}, -7, "days"),
   IF(
      AND({Channel} = "Push", {Send Size} = "Small"),
      "N/A",
      ""
   )
)
​

Now you have formulas in each date field that calculate the due dates based on the Channel and Send Size. The due dates will be automatically calculated as you input different Channels, Send Sizes, and Launch Date values. Note that this solution only covers the two cases you provided. You can add more nested IF statements following the same pattern if you have more possibilities.

Let me know if you have any questions or need further assistance!

[airballer86]

@joshsorenson The solution works but for Approval Due Date when I use "N/A" it breaks the date formatting in the field. Under the formatting tab it says "Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date."