MIN and COLLECT funtion not working :-(
All,
Help please. Can't figure how why my formula not working.
I'm trying find earliest start date for all actvities referencing college = 'Caldwell"
=MIN(COLLECT([Start Date]:[Start Date], College:College, "Caldwell"))
Error: #UNPARSEABLE
Thanks much
Best Answer
-
@Caravan There is no such error called #INVALID COLUMN NAME. Is it perhaps #INVALID COLUMN VALUE? If so, this indicates that there's a column type issue with the summary column. Double check that the summary field is a Date field.
If it is a Date column, then it might be an issue since the Start Date column is a system Date/Time column. Try wrapping your formula in the DATEONLY function:
=DATEONLY(MIN(COLLECT([Start Date]:[Start Date], College:College, "Caldwell")))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Hello @Caravan
Your syntax for the formula appears correct. Check your column names. Do they appear as colored names in your formula? Are you in a region where semicolons are used instead of commas? Your quote marks look like straight quotes here but if they are actually curly (slanted) quotes, they will throw an error.
If you take a screenshot of your actual formula, I might be able to tell more.
Kelly
-
Hi Kelly,
Here you go. Formula is used in sheet summary.
-
Thanks for the screenshot. The syntax of the formula in the screenshot isn't what smartsheet is expecting. Is the Start Date - Caldwell the same formula as shown in this End date formula, just [start date] vs [end date]? The formula you posted initially is a different formula.
Try this
=MIN(COLLECT([Start Date]3:[Start Date]6, [Start Date]3:[Start Date]6, @cell<>"")
Will this work for you? (Make sure your summary field is formatted as a DATE column)
Kelly
-
Oops. wrong screenshot
-
I now saw what I did wrong. I'd transposed my data elements. Thanks so much
-
I spoke too soon. I'd updated my data fields to be in the right position, I received error " Invalid Column Name"
When I changed formula to be (below) which is what the ultimate outcome, I get "Unparseable" error.
-
You need a colon ( : ) right here:
If this still doesn't work, make sure your Start Date column is formatted as a Date column. Also try your formula in an empty cell in another date column in the sheet and see if it works there.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you Jeff. I now have "INVALID COLUMN NAME" :-(. This formular is in the Summmary Sheet. Start/End dates are system dates
-
@Caravan There is no such error called #INVALID COLUMN NAME. Is it perhaps #INVALID COLUMN VALUE? If so, this indicates that there's a column type issue with the summary column. Double check that the summary field is a Date field.
If it is a Date column, then it might be an issue since the Start Date column is a system Date/Time column. Try wrapping your formula in the DATEONLY function:
=DATEONLY(MIN(COLLECT([Start Date]:[Start Date], College:College, "Caldwell")))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, you are correct in all aspects. Thanks a bunch
-
@Caravan Glad I could help solve the issue.
The link in my signature line for Smartsheet Formula Error Messages can be very helpful for figuring out why a formula is really failing!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!