How do I display a Max Date for Child Tasks only if there are no blank Children?

Jake Gustafson

I have a sheet with many hierarchies of Parent and Child Tasks. I want to display the Max Date of the Children, but only when there are not any blank values for the Children.

Objective 1 - Completion Date 8/10/20

• Task A - Completion Date 8/7/20

• Task B - Completion Date 8/10/20

Objective 2 - Completion Date TBD or blank

• Task C - Completion Date 8/11/20

• Task D - Completion Date TBD or blank

I feel like this should be an easy IF statement, but I'm not sure how to setup the syntax. I'm not sure if or how to combine the CHILDREN and ISBLANK functions.

If the Child Tasks are blank, then leave blank, otherwise use MAX(CHILDREN()).

Kelly Moore

Hello Jake,

Although there are probably slicker ways to do this, here's what I came up with. My formula is in the parent row, in the column [Completion Date]. I took into account your 'TBD' answers in your Date column (and you need your column properties to be a Date column to recognize the max). Since you are allowing text in this date column, make sure you leave the "Restrict to Dates Only" unchecked.

=IF(OR((COUNTIF(CHILDREN(), "TBD") = 0), (COUNTIF(CHILDREN(), "") = 0)), MAX(CHILDREN()))

If any of the Children have either "TBD" or Blank, the COUNTIF function will be greater than zero. The IF statement forces the equation to False if the Countif is greater than zero. If the equation is true (no blanks, no TBDs), the Max(Children()) will execute, otherwise the Parent cell remains blank.

Be careful, the placement of the parentheses are important.

-Kelly

David Joyeuse

Hi Jake,

KDM's solutions works fine, but here is another way to do it as well:

In your parent row:

=IF(COUNTIF(CHILDREN(),NOT(ISDATE(@cell)))=0,MAX(CHILDREN()),'')

As long as one of your children is not a date, it'll display nothing (allowing you to go on with TBD, Blank or whatever you want to put in it). If the Count is equal 0, means that every children is a date. You can then display the max of them.

Jake Gustafson

=IF(OR((COUNTIF(CHILDREN(), "TBD") < 0), (COUNTIF(CHILDREN(), "") = 0)), MAX(CHILDREN()))

The above formula worked for me. It is an adaptation of KDM's solution. All I did was change the '= 0' to '< 0'. I don't actually have any TBD values, just dates or blanks.

I'm guessing my issue with your solution David, was that I wasn't sure what to do with @cell. That was maybe supposed to be some value or specific entry, but I wasn't sure.

I really appreciate the help here. I was nowhere near the solutions that you provided.

David Joyeuse

Well, the @cell is a reference to tell smartsheet to do the test on every cell of the specified range.

In my formula the range is CHILDREN(). So Smartsheet will test for every Children cell if it is a date or not. If it is a date, the count doesn't happen for the cell.

Thus, if the result of the COUNTIF function is 0, means every cell is a Date. Statement is then TRUE, so display the max of the children.

@cell & @row are really two really useful references within Smartsheet that can make wonders.

Now, in KDM's formula if you have no "TBD", you can simply get rid of it and remove the OR function to just test COUNTIF(CHILDREN(),""). I believe KDM gave you this tip just in case you may add some other value in the future.

Jake Gustafson

=IF(COUNTIF(CHILDREN(), "") > 0, "", MAX(CHILDREN()))

Thanks again for your patience with me and helping to get something functional and not too clunky.