I need the MAX date of a column IF another column CONTAINS a specific string

bonzemail

I wrote this, and have tried several versions of it based on other community discussions, but I cannot seem to get it to work:

=MAX(COLLECT([Last Updated By Dev]:[Last Updated By Dev], [Version Affected]:[Version Affected], CONTAINS("CU7", @cell)))

This is a sample view of my sheet:

As you can see, it is pulling the date even when the Version Affected row does NOT contain CU7.

Kelly Moore

Hey @bonzemail

I believe you are wanting the Max Date only IF the version affected has your string. The Collect function is filtering data that is in the collection, but is not evaluating that you only want the formula to run when criteria is met. An IF statement will do that for you.

=IF(CONTAINS("CU7", [Version Affected]@row), MAX(COLLECT([Last Updated By Dev]:[Last Updated By Dev], [Version Affected]:[Version Affected], CONTAINS("CU7", @cell))))

I wasn't sure if you actually needed the criteria of your [Version Affected], or if you only wanted the max([Last Updated By Dev]:[Last Updated By Dev]). Your process determines this.

Will this work for you?

Kelly

Kelly Moore

Hey @bonzemail

I believe you are wanting the Max Date only IF the version affected has your string. The Collect function is filtering data that is in the collection, but is not evaluating that you only want the formula to run when criteria is met. An IF statement will do that for you.

=IF(CONTAINS("CU7", [Version Affected]@row), MAX(COLLECT([Last Updated By Dev]:[Last Updated By Dev], [Version Affected]:[Version Affected], CONTAINS("CU7", @cell))))

I wasn't sure if you actually needed the criteria of your [Version Affected], or if you only wanted the max([Last Updated By Dev]:[Last Updated By Dev]). Your process determines this.

Will this work for you?

Kelly

bonzemail

Hey Kelly!

That worked splendidly! Thank you SO much. I had tried with the IF statement first, but where I failed in that attempt was to include the CONTAIN in both statements! You are a gem, thank you!

Kelly Moore

It's my pleasure. Don't hesitate to come back to the community anytime you have a question. We're all here to help.