# Parent Row formula for Max date + 540 days

Options
✭✭✭✭✭✭
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

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

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

That worked! Thank you very much!

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

Thank you!

• ✭✭✭✭✭✭
Options