How to display unique dates from a sheet (source) to another sheet



I tried searching on this topic, and found a formula that worked for someone else.

=IFERROR(INDEX(DISTINCT(COLLECT({Column 1}, {Column 1}, AND(ISDATE(@cell), @cell >= TODAY(-45)))), [Row #]@row), "")

While I am trying to figure how to use it for my needs.

My sheet has column name "Visit_Date" with listing of dates

My sheet I name a column name "Unique_Visit_Date" that would show the only unique dates in Visit_Date

I tried entering the formula in the cell for Unique_Visit_Date, but it gives incorrect argument. Not sure the issue or error?

=IFERROR(INDEX(DISTINCT(COLLECT(Visit_Date@row},Visit_Date@row, AND(ISDATE(Visit_Date)))) "")



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your syntax is off. You forgot one of the ranges at the beginning, the Visit_Date@row is not needed, you don't need the AND function, the ISDATE function needs to have "@cell" in it, and you forgot to tell the formula which entry to pull (first, second, third, etc.).

    =IFERROR(INDEX(DISTINCT(COLLECT({Column To Pull From}, {Column To Pull From}, ISDATE(@cell))), need a number here), "")

  • Bang

    Thanks Paul,

    Your formula helped resolve my formula to yield a date in the cell, but it's off from my intent.

    I have 4 dates in the columns as example; another column call Unique Visit Date I want to have only unique dates show up.

    VisitDate UniqueVisitDate

    4/17/24 4/17/24

    4/17/24 4/29/24

    4/19/24 4/15/24


    So, working with the revised formula with your help, and understanding what the row_index is, for the index formula, I added a number "3" and that show the 3rd unique date, which is 4/15/24.

    =IFERROR(INDEX(DISTINCT(COLLECT(VisitDate1:VisitDate8, VisitDate1:VisitDate8, ISDATE(@cell))), 3), "error")

    My additional question, is how do I autopopulate the UniqueVisitDate column with all unqiue dates, without having to manually change the row_index to pull the unique dates off of?

    Thanks in advance,

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Bang You can manually enter numbers into another column and then use a cell reference. I usually do this.

    =IFERROR(INDEX(DISTINCT(COLLECT(VisitDate1:VisitDate8, VisitDate1:VisitDate8, ISDATE(@cell))), [Number Column]@row), "error")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!