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

Options
Jennifer Lenander
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)



Best Answer

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    Answer βœ“
    Options

    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.

    πŸ‘πŸ˜ŽπŸ‘

Answers

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    Answer βœ“
    Options

    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
    Jennifer Lenander ✭✭✭✭✭
    Options

    Thanks so much, Cody!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!