Index-Collect

charish

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.

1. They can be all dates.
2. They can be dates and "DFD + Date" (example: DFD 3/10/2025)
3. 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):

1. I want the formula to return max date if all the values across all tasks for a specific feature are dates (type 1)
2. I want the formula to return the the first DFD value if there are both dates and "DFD + Date" (type 2)
3. 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))))

1. I want to automate the row_index in the index function to return the first index where the DFD value is found
2. 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.

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. 👍️

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:

=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. 👍️