# Help Creating Conditional Date Based on Single Select Feild

Topic Labels: Formulas
Solved
1983 7
cancel
Showing results for
Did you mean:
4 - Data Explorer

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!

1 Solution

Accepted Solutions
14 - Jupiter

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

Try something like this:

``````SWITCH(
"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.

7 Replies 7
10 - Mercury

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

14 - Jupiter

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

Try something like this:

``````SWITCH(
"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.

18 - Pluto

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(
"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:

14 - Jupiter

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

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

14 - Jupiter

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:

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.

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