MAX children date if another children column is not blank

Clifton Gillette

I am having an issue with a formula. I need to capture the children's max date for Closed Date only if all children's Location cells that are not blank have a Close date.

With the sample below

1. Currently, the Parent Close date would be blank as not all of the Locations 1-4 have a Close Date.
2. If all four locations had dates, the MAX date would be provided
3. The rows below Location 4 are omitted as the Location is blank

I hope I explained this well. Thank you in advance.

Carson Penticuff

Give this a try:

=IF(COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell)), CHILDREN([Close Date]@row), ISDATE(@cell)) = COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell))), MAX(COLLECT(CHILDREN([Close Date]@row), CHILDREN([Close Date]@row), ISDATE(@cell))))

Carson Penticuff

Give this a try:

=IF(COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell)), CHILDREN([Close Date]@row), ISDATE(@cell)) = COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell))), MAX(COLLECT(CHILDREN([Close Date]@row), CHILDREN([Close Date]@row), ISDATE(@cell))))

Clifton Gillette

That worked. Thank you very much for your time.

Carson Penticuff

👍