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

Suriya

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

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

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