JOIN(COLLECT (but only collect dates, not text)

I am trying to collect a date from another sheet column based on criteria and exclude anything that is not a date. Currently I am using the following formula, but it is returning values that are not dates such as N/A. How can I only collect DATES?

=JOIN(COLLECT({OptiFlex Renewal Tracker Range 1}, {OptiFlex Renewal Tracker Range 2}, =[Renewal Date]@row, {OptiFlex Renewal Tracker Range 3}, =[Premera Group #]@row))

Find more posts tagged with

Accepted answers

All comments

Dale Murphy

Hillary;

First, how about: IF(ISDATE([The cell you are checking]1), "Cell contains a date OR whatever result you want", "Cell doesn’t contain a date")

If that doesn't work, check how bad data is getting into the source sheet. (I recommend controlling for that first).

Second, decide what you want to see if there is NOT a date in the source.

Third, why JOIN? JOIN builds strings, which if you are interrogating only one other column is overkill. Why not INDEX? Or even just DATEONLY? With DATEONLY for example, you could build some error trapping along with ISDATE.

Dale

Dale Murphy

@Hillary Walker Hope that response didn't sound harsh! (it reads that way in hindsight)

I should have asked if you had dates in the source, or if you were constructing them using JOIN to combine year/month/day. You probably can identify poor data with IFERROR, but I don't want to over-simplify what you are dealing with.

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.