Need Help with Latest Date Formula

https://app.smartsheet.com/b/publish?EQBCT=956064bedb7c45b29ffbe0a3d8d31c9d

=IF(COUNT(CHILDREN([Expiration Date]@row )) > 0, IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A", AND(NOT(ISBLANK([Signed SpinCo Letter]@row )), [Signed SpinCo Letter]@row <> "No")), OR(MAX(CHILDREN([Expiration Date]@row )) < TODAY(), MAX(CHILDREN([Expiration Date]@row )) = "N/A"), [Contract Status]@row = "Executed"), "Active", ""), IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A"), [Contract Status]@row = "Executed", OR(ISDATE([Expiration Date]@row ) = false, [Expiration Date]@row >= TODAY())), "Active", "Not Active"))

=IF(

  COUNT(CHILDREN([Expiration Date]@row)) > 0,


  IF(

    AND(

      OR(

        [Signed SpinCo Letter]@row = "Yes",

        [Signed SpinCo Letter]@row = "N/A",

        AND(

          NOT(ISBLANK([Signed SpinCo Letter]@row)),

          [Signed SpinCo Letter]@row <> "No"

        )

      ),

      OR(

        MAX(CHILDREN([Expiration Date]@row)) < TODAY(),

        MAX(CHILDREN([Expiration Date]@row)) = "N/A"

      ),

      [Contract Status]@row = "Executed"

    ),

    "Active",

    ""

  ),


  IF(

    AND(

      OR(

        [Signed SpinCo Letter]@row = "Yes",

        [Signed SpinCo Letter]@row = "N/A"

      ),

      [Contract Status]@row = "Executed",

      OR(

        ISDATE([Expiration Date]@row) = false,

        [Expiration Date]@row >= TODAY()

      )

    ),

    "Active",

    "Not Active"

  )

)

What This Does:

If the row has children:
→ It checks MAX(CHILDREN([Expiration Date])) and other fields.
→ If all match: returns "Active"; otherwise returns "" (blank).
If the row has no children (likely a child or standalone row):
→ It evaluates the current row’s own values to return "Active" or "Not Active".

Happy to help!😁

All comments

KPH

That formula looks like it should be parseable. However, it will only work on parent rows and then only if one child has a date in the expiration date column. Can you share a screenshot of your sheet (hiding anything that should not be publicly visible)?

This might be a better formula to work with removing "No" and Blank from the Signed SpinCo Letter field, but this is giving me an INVALID OPERATION error. Attached is a view of my test sheet.

=IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A"), OR(MAX(CHILDREN([Expiration Date]@row )) < TODAY(), MAX(CHILDREN([Expiration Date]@row )) = "N/A"), [Contract Status]@row = "Executed"), "Active", "Not Active")

Expiration Status.png

https://app.smartsheet.com/b/publish?EQBCT=956064bedb7c45b29ffbe0a3d8d31c9d

Taking @KPH 's point, I update your formula;

=IF(COUNT(CHILDREN([Expiration Date]@row )) > 0, IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A", AND(NOT(ISBLANK([Signed SpinCo Letter]@row )), [Signed SpinCo Letter]@row <> "No")), OR(MAX(CHILDREN([Expiration Date]@row )) < TODAY(), MAX(CHILDREN([Expiration Date]@row )) = "N/A"), [Contract Status]@row = "Executed"), "Active", ""))

=IF(

  COUNT(CHILDREN([Expiration Date]@row)) > 0,

  IF(

    AND(

      OR(

        [Signed SpinCo Letter]@row = "Yes",

        [Signed SpinCo Letter]@row = "N/A",

        AND(

          NOT(ISBLANK([Signed SpinCo Letter]@row)),

          [Signed SpinCo Letter]@row <> "No"

        )

      ),

      OR(

        MAX(CHILDREN([Expiration Date]@row)) < TODAY(),

        MAX(CHILDREN([Expiration Date]@row)) = "N/A"

      ),

      [Contract Status]@row = "Executed"

    ),

    "Active",

    ""

  )

)

I need to include the parent rows in the determination. How can I include those, too?

You're right to want to include the parent row itself in the logic — currently, the formula only checks the children rows using CHILDREN() and doesn't consider the parent row’s own [Expiration Date].

To fix that, you can add a condition that also checks the parent’s own expiration date alongside the max expiration date from the children. Here's how you can adjust the formula:

=IF(

  AND(

    OR(

      [Signed SpinCo Letter]@row = "Yes",

      [Signed SpinCo Letter]@row = "N/A",

      AND(NOT(ISBLANK([Signed SpinCo Letter]@row)), [Signed SpinCo Letter]@row <> "No")

    ),

    OR(

      MAX(CHILDREN([Expiration Date]@row)) < TODAY(),

      MAX(CHILDREN([Expiration Date]@row)) = "N/A",
      [Expiration Date]@row < TODAY(),

      [Expiration Date]@row = "N/A"

    ),

    [Contract Status]@row = "Executed"

  ),

  "Active",

  ""

)

Explanation:

This keeps the original logic for checking children rows (MAX(CHILDREN(...))).
It adds additional conditions for the parent’s own [Expiration Date], so that parent rows with valid expiration logic can also qualify.
You’re now covering both cases in one formula: children or the parent itself can satisfy the expiration condition.

Let me know if you want to apply this to child rows individually as well — that would be a separate formula without CHILDREN().

Thank you so much for your help! This formula is showing me Active when they shouldn't be Active and Invalid Operation for those that should be Active. How can I get the formula to show values for Active and Not Active with no errors? Please let me know if that is possible. Thank you!!

I'm having an issue when applying this formula. It is showing me "Active" when they shouldn't be "Active" and INVALID OPERATION for those that should be "Active". Can we have a value for "Not Active" and "Active" show without the INVALID OPERATION error? Anything with a date less than today is "Not Active". Anything with "N/A" or a date in the future is "Active" with the other conditions applied? Please advise. Thanks so much for your help!

Also, yes, this would be very helpful…

Let me know if you want to apply this to

child rows

individually as well — that would be a separate formula without

CHILDREN()

Thank you so much!!

Also, this would be helpful, too…

Let me know if you want to apply this to

child rows

individually as well — that would be a separate formula without

CHILDREN()

https://app.smartsheet.com/b/publish?EQBCT=956064bedb7c45b29ffbe0a3d8d31c9d

=IF(COUNT(CHILDREN([Expiration Date]@row )) > 0, IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A", AND(NOT(ISBLANK([Signed SpinCo Letter]@row )), [Signed SpinCo Letter]@row <> "No")), OR(MAX(CHILDREN([Expiration Date]@row )) < TODAY(), MAX(CHILDREN([Expiration Date]@row )) = "N/A"), [Contract Status]@row = "Executed"), "Active", ""), IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A"), [Contract Status]@row = "Executed", OR(ISDATE([Expiration Date]@row ) = false, [Expiration Date]@row >= TODAY())), "Active", "Not Active"))

=IF(

  COUNT(CHILDREN([Expiration Date]@row)) > 0,


  IF(

    AND(

      OR(

        [Signed SpinCo Letter]@row = "Yes",

        [Signed SpinCo Letter]@row = "N/A",

        AND(

          NOT(ISBLANK([Signed SpinCo Letter]@row)),

          [Signed SpinCo Letter]@row <> "No"

        )

      ),

      OR(

        MAX(CHILDREN([Expiration Date]@row)) < TODAY(),

        MAX(CHILDREN([Expiration Date]@row)) = "N/A"

      ),

      [Contract Status]@row = "Executed"

    ),

    "Active",

    ""

  ),


  IF(

    AND(

      OR(

        [Signed SpinCo Letter]@row = "Yes",

        [Signed SpinCo Letter]@row = "N/A"

      ),

      [Contract Status]@row = "Executed",

      OR(

        ISDATE([Expiration Date]@row) = false,

        [Expiration Date]@row >= TODAY()

      )

    ),

    "Active",

    "Not Active"

  )

)

What This Does:

If the row has children:
→ It checks MAX(CHILDREN([Expiration Date])) and other fields.
→ If all match: returns "Active"; otherwise returns "" (blank).
If the row has no children (likely a child or standalone row):
→ It evaluates the current row’s own values to return "Active" or "Not Active".

Thank you! This has been very helpful. I appreciate it!