Parent Row formula for Max date + 540 days
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
Comments
-
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)
-
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?
-
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.
-
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
-
That worked! Thank you very much!
-
Another option would be to use the COLLECT function to pull from all cells that have dates.
=MAX(COLLECT(CHILDREN(), CHILDREN(), ISDATE(@cell)) + 540
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you!
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives