Skip to main content

Hi all,

Trying to create a (complicated) IF statement. The idea is that we want to flag if a client hasn’t been contacted in a while, but we have different catagories of contacting. And just becuase the client hasn’t been contacted using one method doesn’t mean they haven’t been contacted using another. Essentially, our contact catagories are tiered and I need a formula that understands that:

If the contact date in the ‘Initial Contact’ column is >=5 days from todays date, it should be flagged via the test “Needs Follow Up” UNLESS any of the other contact catagories have dates in them.


It looks like this at the moment, but it’s not working:

IF(DATETIME_DIFF(TODAY(), {Contact Date}, ‘DAYS’) >=5, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘DAYS’) >=15, ‘Needs Follow Up!’, IF(DATETIME_DIFF(Today(), {Demo Date}, ‘DAYS’) >=30, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘DAYS’) >=10, “Needs Follow Up!”, “”


We have the following catagories, however we rank them like this (lowest to highest)



  • Initial Contact

  • Two-Way contact

  • Demo

  • Proposal


I need the formula to understand that if all columns are populated by a date, it should only pay attention to the “Proposal” column. Having a date in 'Proposal" trumps the date in “Demo”, which trumps the date in “Two-Way”, which trumps the date in “Initial Contact”


Hope this makes sense! Thanks in advance for the help!!

You’ve got the right idea but it looks like your nested ifs are missing their closing parenthesis. If you’re nesting IF blocks, you should have a structure like this:


IF( logic, true, IF( logic, true, false) )


Note that since there are two IF blocks in my example, there are now two parenthesis at the end. Also make sure the whole formula is on one line - rather than split into 4 - although no worries if you do this while editing/discussing for readability. Let me know if you’re still having trouble.

Hi Zollie,

Thanks for the help! It’s still not working unfortunately. I added 4 parenthesis to the end as I have 4 IF statements, and I am still getting the same error repsonse. The entire formula is on one line yes, just structured it this way here for readability!


Many thanks for your help!

Hi Zollie,

Thanks for the help! It’s still not working unfortunately. I added 4 parenthesis to the end as I have 4 IF statements, and I am still getting the same error repsonse. The entire formula is on one line yes, just structured it this way here for readability!


Many thanks for your help!


Can you reply with the formula you just tried?

IF(DATETIME_DIFF(TODAY(), {Contact Date}, ‘DAYS’) >=5, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘DAYS’) >=15, ‘Needs Follow Up!’, IF(DATETIME_DIFF(Today(), {Demo Date}, ‘DAYS’) >=30, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘DAYS’) >=10, “Needs Follow Up!”, “”))))

IF(DATETIME_DIFF(TODAY(), {Contact Date}, ‘DAYS’) >=5, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘DAYS’) >=15, ‘Needs Follow Up!’, IF(DATETIME_DIFF(Today(), {Demo Date}, ‘DAYS’) >=30, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘DAYS’) >=10, “Needs Follow Up!”, “”))))


There’s possibly an issue with the Two way & Demo date formulas. I have tried the 4 legs of the formula on their own to ensure they work, but those two don’t give the correct response. Airtable accepts them, however the correct return is not issued in the column?

There’s possibly an issue with the Two way & Demo date formulas. I have tried the 4 legs of the formula on their own to ensure they work, but those two don’t give the correct response. Airtable accepts them, however the correct return is not issued in the column?


You’re probably right. Not seeing any obvious syntax errors. Commas and parenthesis look good with your current formula.


Getting them working in isolation is your best bet. Make sure they’re all actually date fields. I know they’re named that but I can’t see their field types.


Also if you’re going to have the same message (‘Needs Follow Up!’) for each, you can simplify the logic a bit like this:


IF(

    OR(

         DATETIME_DIFF(TODAY(), {Contact Date},‘days’) >=5,

         DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘days’) >=15,

         DATETIME_DIFF(Today(), {Demo Date}, ‘days’) >=30,

         DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘days’) >=10

     ),

     “Needs Follow Up!”,

     ""

)

You’re probably right. Not seeing any obvious syntax errors. Commas and parenthesis look good with your current formula.


Getting them working in isolation is your best bet. Make sure they’re all actually date fields. I know they’re named that but I can’t see their field types.


Also if you’re going to have the same message (‘Needs Follow Up!’) for each, you can simplify the logic a bit like this:


IF(

    OR(

         DATETIME_DIFF(TODAY(), {Contact Date},‘days’) >=5,

         DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘days’) >=15,

         DATETIME_DIFF(Today(), {Demo Date}, ‘days’) >=30,

         DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘days’) >=10

     ),

     “Needs Follow Up!”,

     ""

)

Thta’s great Zollie thanks! I’ll keep at it and see if I can fix them separately. Really appreciate that tip also!


Cheers!

Reply


Terms & Conditions