Index-Collect

I have a feature column, a task (multiple tasks per feature) column, a task date column and a feature date column.

The task dates can be of three different types.

They can be all dates.
They can be dates and "DFD + Date" (example: DFD 3/10/2025)
TBDs

Once the feature, task and task date columns are filled, I want to automatically populate the feature date column (same value across all tasks for that feature):

I want the formula to return max date if all the values across all tasks for a specific feature are dates (type 1)
I want the formula to return the the first DFD value if there are both dates and "DFD + Date" (type 2)
I want the formula to return TBD, if there is only TBD (type 3)

The formula i have:

=IF(CONTAINS("TBD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), "TBD", IF(CONTAINS("DFD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), INDEX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row, [Task Date]:[Task Date], CONTAINS(@cell, [Task Date]@row)), 2), MAX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row))))

I want to automate the row_index in the index function to return the first index where the DFD value is found
For type 2, if there are both dates and DFDs, I am getting a invalid value error

Sorry for the long message and appreciate your help.

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=IF(CONTAINS("TBD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), "TBD", IF(CONTAINS("DFD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), INDEX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row, [Task Date]:[Task Date], CONTAINS("DFD", @cell)), 1), MAX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row))))

charish

Worked like a charm. You are a rockstar :) 😃

Paul Newcome

Happy to help. 👍️

All comments

Paul Newcome

Where exactly is this formula going? Are you able to provide a screenshot for context?

charish

Thanks for your response. Updated the formula after playing it with for a bit more, added screenshot for your reference. The formula is going into the Feature Date (calculated) Column.

For clarity, I have not included all the features or the task column associated with the feature below. Child tasks for a given feature (which can all be different) is what results in different dates in the task date column.

The only issue that I am running into now is the manual row_index in the index formula - I want to pick the first index which has "DFD*"

=IF(CONTAINS("TBD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), "TBD", IF(CONTAINS("DFD", COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row)), INDEX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row), 2), MAX(COLLECT([Task Date]:[Task Date], [Feature]:[Feature], [Feature]@row))))

Paul Newcome

Try this:

charish

Worked like a charm. You are a rockstar :) 😃

Paul Newcome

Happy to help. 👍️

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.