Skip to main content

Hi all,


I am attempting to write a formula to tell me if a contact is overdue or not.

I have contacts that I am to get in touch with on a Weekly, Monthy, Bi-Monthly, and yearly basis.

Below, I have written a formula to tell me if someone who is tagged “weekly” is overdue or not. However, How can I have the formula caulculate for those who are tagged, Monthly, Bi-Monthly, and Yearly? Any input would be much appreciated.


IF(Frequency = “Weekly”, IF(DATETIME_DIFF(TODAY(),{Last Contacted}, ‘DAYS’)> 7, “OVERDUE”, “DUE”))


Thanks!

I would try something like this…


IF(DATETIME_DIFF(TODAY(), {Last Contacted}, 'days') > SWITCH(Frequency, "Weekly", 7, "Monthly", 30, "Bi-Monthly", 60, "Yearly", 365), "OVERDUE", "DUE")

EDIT: I’m never sure how to define “Bi-Monthly” 😂 Might want to change “60” to “15” if you mean twice a month.

I would try something like this…


IF(DATETIME_DIFF(TODAY(), {Last Contacted}, 'days') > SWITCH(Frequency, "Weekly", 7, "Monthly", 30, "Bi-Monthly", 60, "Yearly", 365), "OVERDUE", "DUE")

EDIT: I’m never sure how to define “Bi-Monthly” 😂 Might want to change “60” to “15” if you mean twice a month.


Great! thank you so much. Apologies for the confusion.


Works perfectly.

Reply


Terms & Conditions