Parent Row formula for Max date + 540 days

Peggy Parchert

I'm trying to use a formula in the Parent row of several date columns that will:

1) give me the MAX date from the child (anywhere from 1-12 rows)

2) add 540 days to that date (from #1)

This will work using: =MAX(CHILDREN()) + 540

Except if the child row is blank and then I get the error message #DATE EXPECTED

I've attached a screenshot. Any assistance would be greatly appreciated.

Peggy

L_123

do you just want to get rid of the error when the children are blank?

=iferror(MAX(CHILDREN()) + 540,"")

or we can say 540 days from today if the children are blank?

=iferror(MAX(CHILDREN()) + 540,today()+540)

Peggy Parchert

I was hoping to just leave the Parent row blank if there was no dates in the Child row(s).

I had tried the suggestion

=iferror(MAX(CHILDREN()) + 540,"") - I'm so new at using IFERROR

But I'm still getting the error message #DATE EXPECTED. Suggestions?

L_123

Alright, i think that is a glitch on smartsheet's side. That said, I think I can get around it.

 

=if(not(iserror(MAX(CHILDREN()) + 540)),MAX(CHILDREN()) + 540

Let's see if that works.

Peggy Parchert

Ugh - nope that didn't work either - same error message. 

Andrew Stewart

Hi Peggy,

Your formula works fine for a numeric column. I think the problem occurs because the max function is expecting at least one date, and throwing an error only on parent rows without at least one date to work with. I tested the following, and it seems to work OK as a workaround:

=IF(COUNTIF(CHILDREN(), NOT(ISBLANK(@cell))) <> 0, MAX(CHILDREN()) + 540)

 

Cheers,

Andrew

 

 

Peggy Parchert

That worked! Thank you very much!

Paul Newcome

Another option would be to use the COLLECT function to pull from all cells that have dates.

 

=MAX(COLLECT(CHILDREN(), CHILDREN(), ISDATE(@cell)) + 540

Peggy Parchert

Thank you!

Paul Newcome

Happy to help!