Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

IF Formula using date contacted column, frequency cycle, and calculates next date to contact

Topic Labels: Formulas
Solved
Jump to Solution
659 6
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a Table in which I track all my contacts of organizational partners. I log the date I contacted them, and them I have a frequency that I set for that partner (the frequency can change based on time of year or other circumstances so each record needs the frequency).

There are 5 different cycles (3 Weeks, 6 Weeks, Quarterly, Bi-Annually, Annually) and there are three options that I need to return NOTHING in the formula.

I believe this is a nested if statement, but the formula is throwing me off because of the date contacted column needing to have different amounts of days to return a specific date in the future. Any help would be appreciated. I’m happy to answer questions. I’ve included screenshot in case it helps at all.!Screen Shot 2020-06-19 at 7.21.57 PM

1 Solution

Accepted Solutions

Hey @Matt_Gorman - the problem is that you are trying to do math with the output of a Date field. Airtable’s formula syntax doesn’t allow you to just add a number to a date like that. So it’s complaining at you and saying “NaN”, or “Not a Number”, to let you know that it doesn’t know what to add 21 to.

You can use the DATEADD() function to add days to a date, though, and you can also use a SWITCH() function to make your formula a little more concise and readable:

SWITCH(
   {Contact Cycle},
   "3 Weeks", DATEADD({Date Contacted}, 21, 'days'),
   "6 Weeks", DATEADD({Date Contacted}, 18, 'days')
)

See Solution in Thread

6 Replies 6

What does your current formula look like?

Go easy on me… I’m an AirTable NOOB. This formula is only a couple in line as I was testing the string, but I always had NaN return as the output data. :slightly_smiling_face: Screen Shot 2020-06-19 at 7.28.20 PM

Hey @Matt_Gorman - the problem is that you are trying to do math with the output of a Date field. Airtable’s formula syntax doesn’t allow you to just add a number to a date like that. So it’s complaining at you and saying “NaN”, or “Not a Number”, to let you know that it doesn’t know what to add 21 to.

You can use the DATEADD() function to add days to a date, though, and you can also use a SWITCH() function to make your formula a little more concise and readable:

SWITCH(
   {Contact Cycle},
   "3 Weeks", DATEADD({Date Contacted}, 21, 'days'),
   "6 Weeks", DATEADD({Date Contacted}, 18, 'days')
)

Lol! We would never be harsh on you! :stuck_out_tongue_winking_eye:

I see that @Jeremy_Oglesby has already responded to you!

The thing you were missing is that you need to use the DATE_ADD function, as Jeremy pointed out.

And you can use either SWITCH or a nested IF statement — both would work.

@Jeremy_Oglesby Thank you for your help with this. I’ve enjoyed learning AirTable! Now as I typed it out and add the additional cycles into the code it’s working beautifully!

@ScottWorld I appreciate the kindness! :grinning_face_with_big_eyes: I’m going to keep practicing, asking questions, and learning in order to improve. I appreciate the response!