IF statement using Last Contacted and Frequency

Topic Labels: Formulas
1108 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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”))


2 Replies 2
10 - Mercury
10 - Mercury

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” :joy: Might want to change “60” to “15” if you mean twice a month.

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

Works perfectly.