MIN and COLLECT formula not working

Hi all,

I am trying to return the oldest date from a date column where a checkbox is checked in another. The formula I currently have is below

=MIN(COLLECT({Change Requests Range 3}, {Change Requests Range 5}, 1))

The error I get returned is "#INVALID COLUMN VALUE".

I have screenshots below of the range details as they are referencing another sheet.

Can anyone assist with this?

Thanks,

John

Find more posts tagged with

Formulas

Accepted answers

KPH

Hi @John_Foster

It looks like your OriginalDate column could be a date type column. If so, the column with the formula needs to also be in a date type column as you are trying to return a date.

Hope that helps.

All comments

John_Foster

Might be worth noting here that the column OriginalDate is sometimes empty. I am not sure if this would cause an issue with this formula. But thought it worth noting.

The field is being used to identify the oldest date a row was created from a form, but as we have migrated data over from an older system I wanted a way to identify the original date and not the date the row was created.

KPH

Hi @John_Foster

It looks like your OriginalDate column could be a date type column. If so, the column with the formula needs to also be in a date type column as you are trying to return a date.

Hope that helps.

John_Foster

https://community.smartsheet.com/discussion/comment/406504#Comment_406504

Thanks @KPH , this was the issue. All is working now.

KPH

Great news!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.