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

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

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. :slight_smile: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!

1 Like

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

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.