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

04/22/21
Accepted

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], [[email protected], [Closed Date]:[Closed Date], [Closed Date]@row > TODAY(-7)) > 1, 1, 0)



Best Answer

  • Cody HolmesCody Holmes ✭✭✭✭
    Accepted Answer

    Hi Jennifer,

    Try this corrected formula:

    =IF(COUNTIFS(Code:Code, [email protected], [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 [email protected].
    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.

    👍😎👍

Answers

  • Cody HolmesCody Holmes ✭✭✭✭
    Accepted Answer

    Hi Jennifer,

    Try this corrected formula:

    =IF(COUNTIFS(Code:Code, [email protected], [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 [email protected].
    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 LenanderJennifer Lenander ✭✭✭✭✭

    Thanks so much, Cody!

Sign In or Register to comment.