Max Children with Dates or Text

12/31/20
Answered - Pending Review

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

  • Mark CronkMark Cronk ✭✭✭✭✭

    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.

  • Nic LarsenNic 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.CookKelli.Cook ✭✭✭✭✭

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

  • Nic LarsenNic Larsen ✭✭✭✭✭
    edited 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 LarsenNic 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.CookKelli.Cook ✭✭✭✭✭

    Nic, the last formula worked! Thank you!

Sign In or Register to comment.