Finding the earliest date from Children based on the status in other column

Ameya Athalye
Ameya Athalye ✭✭✭✭✭
edited 09/29/20 in Formulas and Functions

Hi all,

I'm trying to find the earliest (Min) date from children based on the value in a different column. So if the value in Asset Status column is not "Completed", "Cancelled" or "On Hold", then consider those children and then find the earliest date(min) from them.

The column where I'm trying to find the earliest date is a date column and Asset Status is a dropdown single select value column.

So Far I've tried:

=MIN(COLLECT(CHILDREN(), [Asset Status]:[Asset Status], @cell <> "Completed", [Asset Status]:[Asset Status], @cell <> "Cancelled", [Asset Status]:[Asset Status], @cell <> "On Hold", [Asset Status]:[Asset Status], @cell <> ""))

But I keep getting un parsable error.

I stumbled upon this thread and @Paul Newcome suggested similar pattern so I guess I'm on the right track?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!