Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

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

Topic Labels: Formulas
Solved
645 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
14 - Jupiter

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')
)
``````
6 Replies 6
18 - Pluto

What does your current formula look like?

5 - Automation Enthusiast

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:

14 - Jupiter

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')
)
``````
18 - Pluto

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.

5 - Automation Enthusiast

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

5 - Automation Enthusiast

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