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

Jennifer Lenander

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)

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:

1. You had an extra bracket right before Code@row.
2. 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.

👍😎👍

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:

1. You had an extra bracket right before Code@row.
2. 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!