Max Children with Dates or Text
Hi All
I am trying to pull either a max date or text into a parent row and I am getting an #invalid data type error. The column is a date column. If I change it to a text column, I receive #invalid column value.
I'm not sure this is the correct formula. I am using =OR("TBD", MAX(CHILDREN()). Any ideas?
Answers
-
Hi @Kelli.Cook ,
Try:
=IFERROR(MAX(CHILDREN(), "TBD")
The formula will return TBD if there aren't any dates.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
You could try this as well.
It'll check if TBD exists, return that if it does. If not, looks for latest date. Change the column references for your own.
=IF(CONTAINS("TBD", [Test Date]:[Test Date]), "TBD", MAX(CHILDREN([Test Date]:[Test Date])))
-
Thanks for the suggestions. I'm still not getting it to return the TBD without an error.
-
Hmm - what error is it giving you? I've wrapped this in an IFERROR now so you can try this one as well:
=IFERROR(IF(CONTAINS("TBD", CHILDREN([Test Install Date]:[Test Install Date])), "TBD", MAX(CHILDREN([Test Install Date]:[Test Install Date]))), "")
I have my column type set to Date type.
-
One more that's a bit cleaner for use in each parent row:
=IF(COUNT(CHILDREN()) > 0, IFERROR(IF(CONTAINS("TBD", CHILDREN()), "TBD", MAX(CHILDREN())), ""))
-
Nic, the last formula worked! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!