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

Hi,

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)))) "")

Help?

Find more posts tagged with

Accepted answers

All comments

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

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

@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")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.