MIN and COLLECT funtion not working :-(

Caravan

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

Jeff Reisman

@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")))

Kelly Moore

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

Caravan

Hi Kelly,

Here you go. Formula is used in sheet summary.

Kelly Moore

@Caravan

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

Caravan

Oops. wrong screenshot

Caravan

I now saw what I did wrong. I'd transposed my data elements. Thanks so much

Caravan

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.

Jeff Reisman

@Caravan

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.

Caravan

Thank you Jeff. I now have "INVALID COLUMN NAME" :-(. This formular is in the Summmary Sheet. Start/End dates are system dates

Jeff Reisman

@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")))

Caravan

Hi Jeff, you are correct in all aspects. Thanks a bunch

Jeff Reisman

@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!