Skip to main content

I’m trying to write a nested if formula in a CRM document. I have a column for the frequency I need to contact someone, a column with the date they were last contacted and a column that I want to say “Overdue” if it’s more than X days since my last contact. For instance, if I have the Frequency marked Monthly, I want it to say “Overdue” if it’s been more than 30 days since my last contact.


Here’s what I have now, but it doesn’t say “Overdue” when it should. Everything in the column is blank.

IF(AND(Frequency="Weekly",DATETIME_DIFF({Last Contact},DATEADD(Frequency,7,'days'))>0),"Overdue",
IF(AND(Frequency="Monthly",DATETIME_DIFF({Last Contact},DATEADD(Frequency,30,'days'))>0),"Overdue"))
 
 
Try this:
IF(
  SWITCH(
    Frequency,
    'Weekly', 7,
    'Monthly', 30
  ) < DATETIME_DIFF(TODAY(), {Last contacted}, 'days'),
  'Overdue'
)

 

Thank you for your help! I went back and updated my original formula, first. I realized that the Datetime_Diff function should have been between the {Last Contact} and Today, rather than adding days to the last contacted date. After I fixed that, my original formula worked.

 

But I really like the ease of your formula using Switch, and I ultimately used that one, once I made sure i understood how it worked.

Reply


Terms & Conditions