Issue with "And" Conditional Statements

[Colin_James_Bel]

OK,
I am building a personal relationship manager. I am trying to set a column to calculate a yes/no variable if 2 conditions are met (to tag a row “Overdue” if it’s been over 45 days since I last contacted them in the date column next to it). I have the code for 1 condition, and it works.

IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>45,“Overdue”)

I want to add a condition to filter Tags I have, so I can set different day counts for different tags.

In pseudo code, it would be like this:
IF [TAG=Family AND](DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>45,“Overdue”)
IF [TAG=Friends AND](DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>30,“Overdue”)
IF [TAG=Networking AND](DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>60,“Overdue”)

Can anyone help?

[JonathanBowen]

Hi @Colin_James_Belyea - using SWITCH and IF in combo will do the trick:

SWITCH(
  Tags, 
  'Family', IF(DATETIME_DIFF(TODAY(), {Last Catchup}, 'days') > 45, 'Overdue'),
  'Friends', IF(DATETIME_DIFF(TODAY(), {Last Catchup}, 'days') > 30, 'Overdue'),
  'Networking', IF(DATETIME_DIFF(TODAY(), {Last Catchup}, 'days') > 60, 'Overdue')
) 

JB

[Colin_James_Bel]

JonathanBowen:

SWITCH( Tags, ‘Family’, IF(DATETIME_DIFF(TODAY(), {Last Catchup}, ‘days’) > 45, ‘Overdue’), ‘Friends’, IF(DATETIME_DIFF(TODAY(), {Last Catchup}, ‘days’) > 30, ‘Overdue’), ‘Networking’, IF(DATETIME_DIFF(TODAY(), {Last Catchup}, ‘days’) > 60, ‘Overdue’) )

Thank you! Works perfectly.

[Justin_Barrett]

Because all three checks involve date comparisons, here’s another way to structure it by nesting the SWITCH inside a single DATETIME_DIFF:

IF(
    DATETIME_DIFF(
        TODAY(),
        {Last Catchup},
        'days'
    ) > SWITCH(
        Tags,
        'Family', 45,
        'Friends', 30,
        'Networking', 60
    ), 'Overdue'
)