Max Children with Dates or Text

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?


  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Kelli.Cook ,



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



    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:


  • Kelli.CookKelli.Cook ✭✭✭✭✭

    Nic, the last formula worked! Thank you!

Sign In or Register to comment.