Help

Re: Using DateAdd Formula

Solved
Jump to Solution
2476 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jenna_Nystrom
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
Screen Shot 2020-12-17 at 11.27.26 AM

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!

1 Solution

Accepted Solutions
Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

6 Replies 6
augmented
10 - Mercury
10 - Mercury

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

Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

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!

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!