Skip to main content

Hello y’all. I’m trying to setup a table so that dates can change in a column based on data in the other column.


Here’s how it works.


In one column is the Overall Due Date for a task (let’s say it’s August 20th). In the next column, I have a dropdown menu field called “Status.” The options there are “Need to be recorded,” “Need to be Edited,” and “Need to be Uploaded.” So let’s say the current status of an item is “need to be edited.” I would like to set it up so that in a third column, a date could be generated that calculates a new date based on that specific status. So “Need to be recorded” calculates a date 10 days in advance of the overall due date, “Need to be edited” calculates a date 3 days in advance of overall due date, and “Needs to be updated” is 0 days in advance of overall due date. I’m fairly new to Airtable, so unclear if this is at all possible or if it’s too complicated. Any help is appreciated!

The formula field will need three parts: an IF() statement to check if the overall due date is filled in, a SWITCH() function to test the value of the status field, and a DATEADD() function to calculate the date:


IF({Overall Due Date},DATEADD({Overall Due Date},SWITCH({Status},'Need to be recorded',-10,'Need to be edited',-3,'Needs to be updated',0),'days'))

This is so helpful, thanks Kamille! Question for you. So, extrapolating for this formula, for a different table, I wrote out this formula:


IF({Recording Date},DATEADD({Recording Date},SWITCH({Status},‘Outlined’,-3,‘Script Writing (including editing)’,0,’Review Outline’,-2,’Review Story Draft’,-2,’Ready to be Recorded’,0),‘days’))


With the goal being to adjust the date off of the “Recording Date” based off the status. This has worked well. However, when I tried a similar approach for a column called “Upload Date,” the formula doesn’t work. Here it is:


IF({Upload Date},DATEADD({Upload Date},SWITCH({Status},‘Recorded and Ready to be Edited’,-5,’Requests for Re-records’,-3,’Sound Edited and Ready for Review’,-2,’Complete and Ready to be Uploaded’,0),’days’))


Do you by any chance see an error in how I’ve written out this second formula?

This is so helpful, thanks Kamille! Question for you. So, extrapolating for this formula, for a different table, I wrote out this formula:


IF({Recording Date},DATEADD({Recording Date},SWITCH({Status},‘Outlined’,-3,‘Script Writing (including editing)’,0,’Review Outline’,-2,’Review Story Draft’,-2,’Ready to be Recorded’,0),‘days’))


With the goal being to adjust the date off of the “Recording Date” based off the status. This has worked well. However, when I tried a similar approach for a column called “Upload Date,” the formula doesn’t work. Here it is:


IF({Upload Date},DATEADD({Upload Date},SWITCH({Status},‘Recorded and Ready to be Edited’,-5,’Requests for Re-records’,-3,’Sound Edited and Ready for Review’,-2,’Complete and Ready to be Uploaded’,0),’days’))


Do you by any chance see an error in how I’ve written out this second formula?


Your syntax looks fine to me, so I have two ideas:



  • Airtable doesn’t recognize the {Upload Date} field as a date; or

  • When you copied the formula, you have curly apostrophes (’) as opposed to straight ones (').


Does Airtable let you save the field options, are the cells reading ‘Error’, or are the cells blank?

Reply


Terms & Conditions