MIN and COLLECT formula not working

John_Foster

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

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

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!