Max Children with Dates or Text

Kelli.Cook

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

Hi @Kelli.Cook ,

Try:

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

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

Work?

Mark

Nic Larsen

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

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

Nic Larsen

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

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

Nic, the last formula worked! Thank you!