Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

IF statement using Last Contacted and Frequency

Topic Labels: Formulas
585 2
Showing results for 
Search instead for 
Did you mean: 

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

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.