Max Children with Dates or Text

Kelli.Cook · 12/31/20

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?

Mark Cronk · 12/31/20

Hi @Kelli.Cook ,

Try:

=IFERROR(MAX(CHILDREN(), "TBD")

The formula will return TBD if there aren't any dates.

Work?

Mark

Nic Larsen · 12/31/20

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])))

Kelli.Cook · 12/31/20

Thanks for the suggestions. I'm still not getting it to return the TBD without an error.

Nic Larsen · 12/31/20

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.

Nic Larsen · 12/31/20

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())), ""))

Kelli.Cook · 01/11/21

Nic, the last formula worked! Thank you!

Max Children with Dates or Text

Answers

Help Article Resources

Categories