Skip to main content
Solved

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

  • June 20, 2020
  • 6 replies
  • 59 views
M

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

Best answer by Jeremy_Oglesby

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:


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

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 20, 2020

What does your current formula look like?

M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • June 20, 2020

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:

J
Forum|alt.badge.img+18

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:


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')
)
R
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 20, 2020

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:


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.

M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • June 20, 2020

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

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

J
M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • June 20, 2020

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.


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

ScottWorld
Terms & ConditionsAccessibility statement