Help Creating Conditional Date Based on Single Select Feild

[Chelsea_Tompkin]

Hello! I am venturing into some of the more robust AirTable features and am stuck on this formula.

I need to create a formula where a task ‘Start Date’ is extrapolated from the ‘Project Due Date’, based on the ‘Project Status’.

This is what I am attempting: The project due date is 5/30/19

• Task 1 is in the ‘Project Kickoff’ stage
• Task 2 is in the ‘Week 1 - Prototyping’ stage
• Task 3 is in the ‘Week 2 - Design & Development’ stage
• Task 4 is in the ‘Week 3 - Design & Development’ stage

I want the due date for task 1 to be 4 weeks BEFORE the project due date. (5/2/19)
I want the due date for task 2 to be 3 weeks BEFORE the project due date. (5/9/19)
I want the due date for task 3 to be 2 weeks BEFORE the project due date. (5/16/19)
I want the due date for task 4 to be 1 week BEFORE the project due date. (5/23/19)

I’ve been trying to use nested IF formulas, combined with the DATEADD formula, but not having any luck. Help!

[Julian_Kirkness]

Hi Chelsea

Are the Tasks in a separate joined table?

I think the most flexible way to achieve this may be to have a table of Project Task Types which you connect to your tasks table - and against each Task Type you hold the relative number of Weeks (possibly days for greater flexibility) compared to the project due date. In tasks you then also create a lookup field to pick up the relative value and add or subtract it from the project due date (which is itself a lockup from the Project table).

This may not make any sense - let me know if I can help further.

Julian

[Jeremy_Oglesby · Accepted solution]

Hi @Chelsea_Tompkins,

Using a SWITCH() function may work in your situation.

Try something like this:

SWITCH(
   {Task Stage},
   "Project Kickoff", DATEADD({Project Due Date}, -4, 'weeks'),
   "Week 1 - Prototyping", DATEADD({Project Due Date}, -3, 'weeks'),
   "Week 2 - Design & Development", DATEADD({Project Due Date}, -2, 'weeks'),
   "Week 3 - Design & Development", DATEADD({Project Due Date}, -1, 'weeks')
)

I don’t know exactly what your field names are, so replace those with your actual field names.

The SWITCH() function works by looking at the field you define in the first parameter spot of the function (in this case, {Task Stage}, and checks it against each option you provide in order — it will check the value against “Project Kickoff” first, and if it matches, it will process the DATEADD() function following that value. If it doesn’t match, it will move to the next value provided and check it against that one, and so on…

Let me know if it doesn’t work and we can try tweaking it based on your table structure.

[Justin_Barrett]

Because all options involve DATEADD, here’s an alternate way to structure it, by putting SWITCH inside a single DATEADD and only returning the relevant offset value:

DATEADD(
    {Project Due Date},
    SWITCH(
       {Task Stage},
       "Project Kickoff", -4,
       "Week 1 - Prototyping", -3,
       "Week 2 - Design & Development", -2,
       "Week 3 - Design & Development", -1
    ), "weeks"
)

Also, the quotes in the original version are styled, which will make Airtable complain, so I fixed that as well. :slightly_smiling_face:

[Jeremy_Oglesby]

Haven’t quite figured out how to get my phone to output dumb quotes when I’m responding on my phone :confounded:

Thanks for fixing it

[Justin_Barrett]

Happy to help. I can’t imagine typing formulas on a phone. Or did you copy/paste it from the mobile app?

[Jeremy_Oglesby]

I typed it from my phone. From a computer keyboard, the quotes default to dumb quotes when you type them into a code block (as you know), but apparently from a phone keyboard they do not… dunno if that’s a iOS keyboard issue or an issue with the iOS Discourse app :man_shrugging:t2:

[Justin_Barrett]

Just installed the Discourse app and ran a quick test. To force it to create dumb quotes, hold your finger on the quote button for a second, and a collection of options will appear. The default option, interestingly enough, is a dumb quote. The same goes for the button for an apostrophe/single quote. Not much extra time needed. Tap, hold, release.

Jeremy_Oglesby:

dunno if that’s a iOS keyboard issue or an issue with the iOS Discourse app :man_shrugging:t2:

Probably the latter IMO. Indenting to make a code block should force dumb quotes, like it does via a desktop browser.