MAX Date based on value in a Category column


Hello. I am trying to calculate MIN and MAX dates based on the value in the Milestone Category. The first screenshot is my source sheet. The second screenshot is the second sheet where I am performing the calculations.

Looking at the first screenshot below, let's say I want to calculate the MAX Start Date for any row that contains "Array Assemblies", I would expect a returned value of 3/23/23.

If I use the following formula, it is only looking at row #49 and returning an incorrect value of 3/10/23:

=MAX(COLLECT({Start Date}, {Milestone Category}, "Array Assemblies"))

If I use the following formula, it returns a blank value for some reason:

=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", {Milestone Category})))

Can someone please give me some advice on how to best do this?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!