Skip to main content

Hi!

I’m trying to see if there’s a way I can do this. I know Airtable is not a “spreadsheet” and that typically you cannot use different formulas per row/cell because of the database situation. I’m a complete newb to airtable, so I’m still learning the intricacies to see if it’s something I can use in my role.


I’m putting together a checklist for a large event that has due dates- start and end dates included in the table. In my google form/excel sheet I have formulas created so that all I have to do is put in the first day of the actual event into a column, and then another column has the # of months, days or weeks out that we need to start working on that particular task for the event. I had to go in each cell to change the formula for the specific task for those that were 1+ months out vs. those that needed to be start 2 weeks out, etc.


I’m using this formula in the column (the DATE and Project Start are the columns I’m having it pull data from): DATEADD(Date,-{Project Start (Months/Weeks/Days Out)},‘months’)


Date is pulling from the Date column that i have the first day of the event in. Project Start (Months/Weeks/Days Out) is the column that has the # of Months/Weeks/Days out we need to start working on the task. In the column - right now- I have a whole number if it’s for months (ie. 4) and for the weeks or days tasks I have it listed with # Weeks (IE 2 Weeks, 2 Days).


Is there a formula that I can use in the formula column that will put a correct date in the formula field based on whether the task is a months, weeks or days task?


Here’s an example of the table:


I’m looking to create a template as I will have 4 similar events each year, so it’d be helpful to be able to put the event date and those tasks will automatically fill in their start date.

Thanks so much!

Hi Jenna. How do you feel about standardizing your thinking? Instead of 6 months, how about 180 days? Two weeks, 14 days. You could make that field a single select so that you don’t have to do the math in your head, if you want (not saying you couldn’t). That Project Start column is sub-optimal right now.


If you must keep it as is, there are some semi-complicated formulas that can do what you want. Though you will still need to standardize a little to be able to consistently parse the string in that field.


Let us know which way you decide to go.

Hi Jenna. How do you feel about standardizing your thinking? Instead of 6 months, how about 180 days? Two weeks, 14 days. You could make that field a single select so that you don’t have to do the math in your head, if you want (not saying you couldn’t). That Project Start column is sub-optimal right now.


If you must keep it as is, there are some semi-complicated formulas that can do what you want. Though you will still need to standardize a little to be able to consistently parse the string in that field.


Let us know which way you decide to go.


Thanks so much for your response!


I did think about that but with the higher number of months (2+) it would be easier for me and my team to know months, rather than weeks. 2 Months and under I’m fine moving to weeks, it’s just anything more than that my brain has to do the math. =)

Hi Jenna,

Try this formula. It will do what you want, but only if the number is a single digit. I just noticed that and I have to go do the dishes 🙂. Happy to help get to the finish line if this looks like it will work.


DATEADD({Date of Event},

-LEFT({Months/Weeks/Days Out},1),

IF(FIND('m',LOWER({Months/Weeks/Days Out})),'Months',

IF(FIND('w', LOWER({Months/Weeks/Days Out})),'Weeks','Days')))

Hi Jenna,

Try this formula. It will do what you want, but only if the number is a single digit. I just noticed that and I have to go do the dishes 🙂. Happy to help get to the finish line if this looks like it will work.


DATEADD({Date of Event},

-LEFT({Months/Weeks/Days Out},1),

IF(FIND('m',LOWER({Months/Weeks/Days Out})),'Months',

IF(FIND('w', LOWER({Months/Weeks/Days Out})),'Weeks','Days')))

Ooohhh! This should work. I will give it a try tomorrow and let you know. Thanks so much! Single digit should be fine- assuming that’s in reference to the months/days/weeks, I don’t have anything that would be a double digit number.


Thanks! Will report back soon!

Hi Jenna,

Try this formula. It will do what you want, but only if the number is a single digit. I just noticed that and I have to go do the dishes 🙂. Happy to help get to the finish line if this looks like it will work.


DATEADD({Date of Event},

-LEFT({Months/Weeks/Days Out},1),

IF(FIND('m',LOWER({Months/Weeks/Days Out})),'Months',

IF(FIND('w', LOWER({Months/Weeks/Days Out})),'Weeks','Days')))

It worked! Thanks so much for thinking this through for me! This is exactly what I was looking for!


Thank you again!

It worked! Thanks so much for thinking this through for me! This is exactly what I was looking for!


Thank you again!


You’re welcome! Glad it worked!

Reply


Terms & Conditions