Parent Row formula for Max date + 540 days

Peggy PPeggy P ✭✭✭✭✭
edited 12/09/19 in Using Smartsheet
08/30/19 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

Screen Shot 2019-08-30 at 2.52.04 PM (2).png

Comments

  • L@123[email protected] ✭✭✭✭✭

    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 PPeggy P ✭✭✭✭✭

    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[email protected] ✭✭✭✭✭

    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 PPeggy P ✭✭✭✭✭

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

  • 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

     

     

    Children Date.JPG

  • Peggy PPeggy P ✭✭✭✭✭

    That worked! Thank you very much!

  • Paul NewcomePaul 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 PPeggy P ✭✭✭✭✭

    Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

Sign In or Register to comment.