Highlight a Line With Overdue Date

[MishQ]

Hi all,

I’m building a CRM and have a field for ‘Contact Frequency’ set as ‘Single select’ with 7, 14, 30, 90 as options.

I have a roll up field with ‘Most Recent Interaction’ which pulls from an ‘Interactions’ table.

I would like to create a field to automatically mark as ‘Overdue’ anyone who’s ‘Contact Frequency’ has been exceeded.

Some searching uncovered a formula which I have tried to adapt no doubt with a glaring error!

I suspect that my ‘Contact Frequency’ options are perhaps not in the correct format or need a separate field to convert them into the correct format before a formula can use them?

IF({Contact Frequency}=””,””,IF(DATETIME_DIFF(TODAY(),{Most Recent Interaction},’days’)>{Contact Frequency},“Overdue”))

Any help would be greatly appreciated!

[Zack_S · Accepted solution]

Hi @MishQ,

The formula seemed to work for me. Using a single select field shouldn’t be a problem.

IF({Contact Frequency}="","",IF(DATETIME_DIFF(TODAY(),{Most Recent Interaction}, 'days')>{Contact Frequency},"Overdue"))

If you copy and paste that directly and it doesn’t work, you could try creating a copy of your base, removing the sensitive data and creating a share link.

Maybe someone else has an idea of the issue top of mind.

[MishQ]

@Zack_S, it works!

Looks like I was missing a space before ‘days’ in mine?

Thank you, very much appreciated!

[Zack_S]

The space shouldn’t be an issue.

The style of quotes in your formula looks off. I’m wondering if that is the issue. Just a guess though.

Glad it worked!

[AliviaF]

Imagine your CRM as a digital filing cabinet. You've got lots of important documents (or in this case, contacts) stored there. But sometimes, things get a bit messy and files can get lost or misplaced. That's where "overdue contacts" come in.

Think of overdue contacts as the overdue library books. They're supposed to be returned by a certain date, but for some reason, they haven't been. In your CRM, it means there's something you need to do or follow up on with these contacts. Maybe it's a meeting, a call, or just a friendly reminder.

Overdue contacts are like a little alarm bell going off in your CRM. It's a way for the system to say, "Hey, don't forget about these guys!" So, when you see an overdue contact, it's a good time to take a closer look and make sure everything's on track.