Retrieving Ancestor (date) value from multi-level parent-child rows

Hi All,

I used the following formula to retrieve ancestor (first parent) date value of the multi-level parent-child rows for each row.

=LEFT(INDEX(JOIN(ANCESTORS(Date@row), ","), 1), 8)

The issue that I'm facing is the return value being "TEXT" despite having "DATE" column type. Since I have to use this return value in comparing other dates, suggest any other alternative to retrieve the value in DATE format

Find more posts tagged with

Sharing and Collaboration

Formulas

Solution Center

Community Platform

Automations

Accepted answers

jmyzk_cloudsmart_jp

Hi @Suriya

You can use the Header value as the search value of the MATCH function.

Find the relative position of the first ANCESTORs value of the Header in the Header:Header range gotten by INDEX(,1).

In English, the formula means to find the top ANCESTORS of a given header and use the relative position to get the corresponding date value.😀

=IF(COUNT(ANCESTORS(Header@row)) = 0, Date@row, INDEX(Date:Date, MATCH(INDEX(ANCESTORS(Header@row), 1), Header:Header, 0)))

https://app.smartsheet.com/b/publish?EQBCT=4cc47c0819914903b05024d2c43f78ed

All comments

jmyzk_cloudsmart_jp

Hi @Suriya

You can use the Header value as the search value of the MATCH function.

Find the relative position of the first ANCESTORs value of the Header in the Header:Header range gotten by INDEX(,1).

In English, the formula means to find the top ANCESTORS of a given header and use the relative position to get the corresponding date value.😀

=IF(COUNT(ANCESTORS(Header@row)) = 0, Date@row, INDEX(Date:Date, MATCH(INDEX(ANCESTORS(Header@row), 1), Header:Header, 0)))

https://app.smartsheet.com/b/publish?EQBCT=4cc47c0819914903b05024d2c43f78ed

Suriya

It works! Thank you @jmyzk_cloudsmart_jp

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.