Finding Next Closest Date of Children

Ok hive mind - I haven't been able to talk this formula out with someone yet, so I am looking for a little help. I have the formula that will fill out the children of my FINAL DATE column, but to fill out the parent row, I want my formula to look at the children of the NON REFUNDABLE DEPOSIT column, and tell me what is the next closest upcoming date in the future. Help!!

As a note, I do have a P column that tells me the ancestors, so basically my formula will be:

=IF(P@row = 1, [NON REFUNDABLE DEPOSIT]@row, IF(P = 0, ?????

I also have a helper column that I am not sure if I need called "NET Days from Today" that basically tells me how many days away the "NON REFUNDABLE DEPOSIT" date is from today. So if I could someone use that and use the closest negative number from zero? Would that help??

Find more posts tagged with

Sheets

Formulas

dates

Accepted answers

Krzysztof Wroblewski

Hi @Michelle Choate 2 ,

You may try this:

Put this formula in the parent/child output column (e.g., your “FINAL DATE” if that’s where you want the result to appear). It uses your P flag (P@row =0 = parent; P@row = 1 = child).

I have put for P column:

=COUNT(ANCESTORS([Primary Column]@row ))

=IF(P@row = 0, MIN(COLLECT(CHILDREN([NON REFUNDABLE DEPOSIT]@row ), CHILDREN([NON REFUNDABLE DEPOSIT]@row ), >TODAY())), IF(P@row > 0, [NON REFUNDABLE DEPOSIT]@row ))

What it does

Parent rows (P = 0): Looks at the children’s NON REFUNDABLE DEPOSIT dates and returns the smallest date that is ≥ TODAY() (i.e., the next upcoming date). If none exist, it returns blank (""), which you can change to a message if you like.
Child rows (P <> 0): Returns the row’s own NON REFUNDABLE DEPOSIT date.

All comments

Krzysztof Wroblewski

Hi @Michelle Choate 2 ,

You may try this:

Put this formula in the parent/child output column (e.g., your “FINAL DATE” if that’s where you want the result to appear). It uses your P flag (P@row =0 = parent; P@row = 1 = child).

I have put for P column:

=COUNT(ANCESTORS([Primary Column]@row ))

=IF(P@row = 0, MIN(COLLECT(CHILDREN([NON REFUNDABLE DEPOSIT]@row ), CHILDREN([NON REFUNDABLE DEPOSIT]@row ), >TODAY())), IF(P@row > 0, [NON REFUNDABLE DEPOSIT]@row ))

What it does

Parent rows (P = 0): Looks at the children’s NON REFUNDABLE DEPOSIT dates and returns the smallest date that is ≥ TODAY() (i.e., the next upcoming date). If none exist, it returns blank (""), which you can change to a message if you like.
Child rows (P <> 0): Returns the row’s own NON REFUNDABLE DEPOSIT date.

Paul Newcome

I would use this:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, MIN(COLLECT(CHILDREN([Non Refundable Deposit]@row), CHILDREN([Non Refundable Deposit]@row), @cell > TODAY())), [Non Refundable Deposit]@row)

Michelle Choate 2

@Krzysztof Wroblewski - Thank you! I knew there was a MIN in there, but just could not get it out of my brain correctly. THANK YOU!!

Krzysztof Wroblewski

Great way to remove need of helper column P

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.