Using DateAdd Formula

[Jenna_Nystrom]

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!

[augmented]

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.

[Jenna_Nystrom]

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. 😃

[Julian_E_Post · Accepted solution]

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')))

[Jenna_Nystrom]

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!

[Jenna_Nystrom]

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

Thank you again!

[Julian_E_Post]

You’re welcome! Glad it worked!