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.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!