Skip to main content

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
IF rTAG=Friends AND](DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>30,“Overdue”)

IF 0TAG=Networking AND](DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>60,“Overdue”)


Can anyone help?

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


Thank you! Works perfectly.


Thank you! Works perfectly.


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'

)

Reply


Terms & Conditions