Skip to main content
Solved

Add Dates Automatically based on Word

  • April 26, 2023
  • 7 replies
  • 89 views
J
Forum|alt.badge.img+2

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.

Best answer by Brian_Swanson

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

 

7 replies

B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • Answer
  • April 26, 2023

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

 

2 Attachments
J
J
Forum|alt.badge.img+2
  • JaclynB
  • Author
  • Participating Frequently
  • April 27, 2023

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

 


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.
B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • April 27, 2023

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.

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!

1 Attachments
J
J
Forum|alt.badge.img+2
  • JaclynB
  • Author
  • Participating Frequently
  • April 27, 2023

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!

B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • April 28, 2023

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!

B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • April 28, 2023

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!


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!
K
Forum|alt.badge.img+1
  • kittie79
  • New Participant
  • March 8, 2024

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 

Terms & ConditionsAccessibility statement