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

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?

https://community.smartsheet.com/discussion/54631/finding-the-earliest-date-in-rows-contingent-on-another-columns-value

Find more posts tagged with

Sheets

Formulas

Accepted answers

SoS | Dan Palenchar

Hi @ameyaa

I made a video showing how to solve this, view it at https://youtu.be/MNCmmavU2HU

You can use this formula in the cell of any parent row where you want to return the earliest date of all children in that row where status does not equal completed, on hold, cancelled, or blank.

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

All comments

SoS | Dan Palenchar

Hi @ameyaa

I made a video showing how to solve this, view it at https://youtu.be/MNCmmavU2HU

You can use this formula in the cell of any parent row where you want to return the earliest date of all children in that row where status does not equal completed, on hold, cancelled, or blank.

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

Ameya Athalye

Thanks @SoS | Dan Palenchar , I'll check it out and update you.

Ameya Athalye

@SoS | Dan Palenchar it worked! Thank you.

KPH

@SoS | Dan Palenchar

Is there a way to include rows with blank statuses? In the example if the Status cell is blank the due date of that row is not included when calculating the minimum due date.

I tried removing @cell <> "" at the end but it didn't make any difference.

SoS | Dan Palenchar

Hello @KPH,

I just saw this 2+ years later, sorry for the delay! I don't know if you still need this but I think this would work.

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

KPH

I did figure it out, but thanks for responding!

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.