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

Jennifer Lenander
Jennifer Lenander ✭✭✭✭✭
in Formulas and Functions

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)


image.png


· Share on FacebookShare on Twitter

Best Answer

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    Answer ✓

    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.

    👍😎👍

    · Share on FacebookShare on Twitter

Answers

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    Answer ✓

    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.

    👍😎👍

    · Share on FacebookShare on Twitter
  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭

    Thanks so much, Cody!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!