Skip to main content
Solved

Multivariable IF/AND formula for my personal CRM

  • October 6, 2020
  • 2 replies
  • 28 views
Z

Hello Airtable community!

I am struggling with a formula and thought I would put it here since you all never let me down :slightly_smiling_face:

I want the formula to produce the result overdue IF

  • the contact’s sum of (“level of connection” and “Level of importance”) is greater than 5 and “Last Contact” is more than 180 Days
  • The contact’s sum of (“level of connection” and “Level of importance”) is greater than 7 and “Last Contact” is more than 90 Days
    -The contact’s sum of (“level of connection” and “Level of importance”) is greater than 8.9 and “Last Contact” is more than 30 Days

For reference here is what I currently have (Very far off)
IF(DATETIME_DIFF(TODAY(),{Last contact},‘days’)>90,“Overdue”)

THANK YOU !!!

Best answer by Nick_Dennis

I would first create a formula field called “Days since contact” to simplify this (you can hide the field after). This formula will tell you the number of days since your {Last contact} date field:
DATETIME_DIFF(TODAY(), {Last contact}, 'days')

Then you can use an IF() statement with OR() to check is any of those these 3 conditions are true:

IF( OR( AND({Days since contact}>180, {Level of connection} > 5, {Level of Importance} > 5), AND({Days since contact}>90, {Level of connection} > 7, {Level of Importance} > 7), AND({Days since contact}>30, {Level of connection} > 8.9, {Level of Importance} > 8.9) ), "Overdue")

That should work as long as the fields are all titled correctly, and you create the new {Days since contact} field. Hope that works for you.

2 replies

N
Forum|alt.badge.img+14
  • Nick_Dennis
  • Participating Frequently
  • Answer
  • October 7, 2020

I would first create a formula field called “Days since contact” to simplify this (you can hide the field after). This formula will tell you the number of days since your {Last contact} date field:
DATETIME_DIFF(TODAY(), {Last contact}, 'days')

Then you can use an IF() statement with OR() to check is any of those these 3 conditions are true:

IF( OR( AND({Days since contact}>180, {Level of connection} > 5, {Level of Importance} > 5), AND({Days since contact}>90, {Level of connection} > 7, {Level of Importance} > 7), AND({Days since contact}>30, {Level of connection} > 8.9, {Level of Importance} > 8.9) ), "Overdue")

That should work as long as the fields are all titled correctly, and you create the new {Days since contact} field. Hope that works for you.

Z
Z
  • Zack_Crilley
  • Author
  • New Participant
  • October 8, 2020

I would first create a formula field called “Days since contact” to simplify this (you can hide the field after). This formula will tell you the number of days since your {Last contact} date field:
DATETIME_DIFF(TODAY(), {Last contact}, 'days')

Then you can use an IF() statement with OR() to check is any of those these 3 conditions are true:

IF( OR( AND({Days since contact}>180, {Level of connection} > 5, {Level of Importance} > 5), AND({Days since contact}>90, {Level of connection} > 7, {Level of Importance} > 7), AND({Days since contact}>30, {Level of connection} > 8.9, {Level of Importance} > 8.9) ), "Overdue")

That should work as long as the fields are all titled correctly, and you create the new {Days since contact} field. Hope that works for you.


This did the trick. Thank you, Nick!

N
Terms & ConditionsAccessibility statement