Skip to main content

IF statement using Last Contacted and Frequency

  • May 13, 2019
  • 2 replies
  • 27 views
K

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!

2 replies

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • May 13, 2019

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.

Justin_Barrett
K
  • Ken_Stilwell
  • Author
  • New Participant
  • May 13, 2019

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.

A
Terms & ConditionsAccessibility statement