Identify Duplicate Values, only within the last 7 days, based on unique identifier (code)

I want to identify when a code is a duplicate in the last 7 days but I'm struggling with the logic/syntax.

Essentially people call in for assistance and we track that. When we put a closed date in, automation automatically sends a survey. However, if they call in more than once in a week, I don't want to send them a survey again. So i'm adding the helper column "dup in last week" so I can use as a condition in my automation. But I'm not getting the right formula. I'm hoping the "Dup in Last week" can be a check box column. Below is what I have tried but it doesn't work. Anyone have ideas?

=IF(COUNTIFS([Code]:[Code], [Code@row, [Closed Date]:[Closed Date], [Closed Date]@row > TODAY(-7)) > 1, 1, 0)

Find more posts tagged with

Accepted answers

Cody Holmes

Hi Jennifer,

Try this corrected formula:

=IF(COUNTIFS(Code:Code, Code@row, [Closed Date]:[Closed Date], [Closed Date]@row > TODAY() - 7) > 1, 1, 0)

It looks like you just needed to make two corrections:

You had an extra bracket right before Code@row.
You needed to take the -7 outside of the TODAY() function and place it just behind it, as I have done above. If you are ever wanting to subtract or add days to today's date, do so in this manner.

Hope that helps! Let me know if you need any more details. Give me a vote if I proved helpful.

👍😎👍

All comments

Cody Holmes

Hi Jennifer,

Try this corrected formula:

=IF(COUNTIFS(Code:Code, Code@row, [Closed Date]:[Closed Date], [Closed Date]@row > TODAY() - 7) > 1, 1, 0)

It looks like you just needed to make two corrections:

You had an extra bracket right before Code@row.
You needed to take the -7 outside of the TODAY() function and place it just behind it, as I have done above. If you are ever wanting to subtract or add days to today's date, do so in this manner.

Hope that helps! Let me know if you need any more details. Give me a vote if I proved helpful.

👍😎👍

Jennifer Lenander

Thanks so much, Cody!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.