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
- Smartsheet Customer Resources
- 63.3K Get Help
- 392 Global Discussions
- 213 Industry Talk
- 447 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!