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))
Answers
-
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
-
@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.
dm
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!