Need Help with Latest Date Formula

Shannon Heward · 06/27/25

I have a sheet where I need to identify if a parent/child record is Active.

Expiration Date
Is in the past or "N/A"

Signed SpinCo Letter
Include entries of "Yes" or "N/A" and do not include "No" or Blank.

Contract Status
Include "Executed"

Here's what I have but I'm getting an UNPARSEABLE ERROR.

=IF(AND(OR([Signed SpinCo Letter]@row = "Yes", [Signed SpinCo Letter]@row = "N/A", 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", "")

There's obviously something I'm missing. :) Could someone please help? Please let me know if more details are needed. Thank you.

jmyzk_cloudsmart_jp · 07/02/25

@Shannon Heward

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

or

=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".

jmyzk_cloudsmart_jp · 07/08/25

Happy to help!😁

KPH · 06/27/25

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

Shannon Heward · 06/27/25

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

jmyzk_cloudsmart_jp · 06/27/25

@Shannon Heward

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

or

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

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

Shannon Heward · 06/27/25

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

jmyzk_cloudsmart_jp · 06/28/25

@Shannon Heward

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().

Shannon Heward · 06/30/25

@jmyzk_cloudsmart_jp

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!!

Shannon Heward · 07/01/25

@jmyzk_cloudsmart_jp

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!

Shannon Heward · 07/01/25

@jmyzk_cloudsmart_jp

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!!

Shannon Heward · 07/01/25

@jmyzk_cloudsmart_jp

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

.

jmyzk_cloudsmart_jp · 07/02/25

@Shannon Heward

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

or

=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".

Shannon Heward · 07/08/25

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

jmyzk_cloudsmart_jp · 07/08/25

Happy to help!😁

Need Help with Latest Date Formula

Best Answers

What This Does:

Answers

What This Does:

Help Article Resources

Categories