Add Dates Automatically based on Word

[JaclynB]

If there are options in a drop down menu (example Quiz or Test), start dates are entered by user, can I add a new column with a formula that detects the word 'quiz' or 'test' in the previous column and add an end date automatically based on the word? A quiz is 3 days after start date and a test is 6 days after start date for example. 

Appreciate any insight.

[Brian_Swanson · Accepted solution]

Jaclyn

Hey there!  This is a great quesion and can be handled in a couple of ways.  I would suggest using a nested If() statement and the DATEADD() function within a formula field.  Setup your table to have the dropdown field, start date field and then the end date field.  End Date will be a formula that looks something like this:

IF({Drop Down}="Test",  DATEADD({start date}, 6,'day'),

IF({Drop Down}="Quiz",DATEADD({start date}, 3,'day'),

DATEADD({start date}, 1,'day')))

This formula will look for the specified terms in the drop down and then add the appropriate amount of time to the start date field within the end date fiedl.  This addition could be done in days, weeks, minutes, etc. 

In the version shown above if the person selects the Other option or any other nondefined option within the formula it will just add a single day.  I have attached a screenshot of the outcome in a simple base as well as an exploded view of the formula field. 

All the best,

Brian

[JaclynB]

Thank you! I tried renaming the drop down to 'Project Name' and updated the formula to reflect it, but it is not working. It will only work if I keep the column named as Drop Down. Any suggestions on that?

Also, what if I want to add video or anything else to the drop down. How do I add those? I tried this and something is off. 

IF({Drop Down}="Test", DATEADD({Start Date}, 6,'day'),

IF({Drop Down}="Quiz",DATEADD({Start Date}, 3,'day'),

IF({Drop Down}="Video",DATEADD({Start Date}, 8,'day'),

DATEADD({Start Date}, 1,'day')))

Last question, how do I have the formula set due dates that only fall Monday-Friday? 

Thank you so much for your helpful and prompt response.

[Brian_Swanson · Accepted solution]

Not certain why you are having trouble with changing the name to Project Name.  From all of my expiereince if you update or change the name of a field, Airtable updates any references to the field in the formulas.  I recreated the base parameters and change the name of my "Drop Down" field to "Project Name" and it updated my formula correctly.  

The formula for the video add looks just about right.  It does appear that you might be missing an extra paren at the end fo the formula but other than that you have it.  The base I threw this in is here if you want to look at it or copy and play with it:  https://airtable.com/shr3QB0wkQibMdKvB   

Otherwise the update formula for video is attached.

Hope this helps and all the best!

[JaclynB]

Hello again,

Question- What if I wanted it to work across all of the activities & their associated SLAs. If this is possible, where do I begin? 

Thank you!

[Brian_Swanson]

HELLO!

I am not quite certain what you are looking for.  Are you hoping to populate a growing list of events "Project Names" and adjust on the fly without having to consistently add to the formula.  There are definitely ways to do that typically by linking in another table.  If you can provide a bit more information that would help. 

Let me know!

[Brian_Swanson]

I updated the shared base from earlier with another option that I think is what you are looking for.

https://airtable.com/shr3QB0wkQibMdKvB

The table "TEST - Linked to Project Names" links the project name field to the options populated in the table "Project Names". In this setup you can easily add new projects and time to deadlines in days.  When the a project name is selected in the "TEST - Linked to Project Names" table it brings over the the number of days to add to the start date to create a deadline.  This field could be hidden if you don't want to see it.  The end date still uses a formula, but it uses the WORKDAY() formula as I missed earlier when you said you wanted to output to only calculate based on days of the workweek.  The formula used is not much simplier:

WORKDAY({Start Date}, {Deadline (from Project Name)})

The result would seem to be easier and simplier to update and change than a long nested IF() statement.  Don't know if that is what you are looking for, but thought I woudl show it.  

All the best!

[kittie79]

Sorry to jump on this chain but, I was able to make this work for what i was needing however i need to set the time is there a way to add that to the formula ? time is either 7 PM or 8am