How to turn off >30 Flag

I have a flag that turns on >30 days after receipt of a document but what I did not realize is the flag applies to all documents >30 days old, even if complete.

What I need is a flag that only activates when two Document Stages are active -- "In Progress" and "Contracting (Customer Review)" the other stages will not apply.

The current working formula for >30 days is =IF([Today's Date]@row < TODAY(-30), 1)

and I attempted to add "In Progress" and "Contracting (Customer Review)" but received multiple syntax errors.

I appreciate your help.


Answers

  • ker9
    ker9 ✭✭✭✭✭✭

    Hi @Joan156

    Please see if this will work for you:

    =IF([Document Stage]@row ="Complete", 0, IF([Today's Date]@row < TODAY(-30), 1))

    Hope this helps!

  • Joan156
    Joan156 ✭✭

    @ker9

    Thanks for responding. I replaced your "Complete" with "Hold" but the other exclusions are not working. They are:

    Hold

    Fully Executed (Closed Won)

    Lost/Canceled (Closed Lost)

    =IF([Document Stage]@row = "Hold", “[Lost/Canceled (Closed Lost)]”, “[Fully Executed (Closed Won)]”, 0, IF([Today's Date]@row < TODAY(-30), 1))

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/18/23

    @Joan156

    What I need is a flag that only activates when two Document Stages are active -- "In Progress" and "Contracting (Customer Review)" the other stages will not apply.

    =IF(OR([Document Stage]@row ="In Progress", [Document Stage]@row ="Contracting"), 1, 0)

    I am presuming that there is only one stage per row in the above formula based on your picture.

    For your other request above:

    =IF(OR([Document Stage]@row = "Hold", [Document Stage]@row=“[Lost/Canceled (Closed Lost)]”, [Document Stage]@row=“[Fully Executed (Closed Won)]”), 0, IF([Today's Date]@row < TODAY(-30), 1))

    ETA NOTE: some of the above include brackets based on your text. I don't know if you were trying to reference columns or text or if the brackets [ ] should or should not be used.

    Depending on how many stages you have it might be easier to exclude a specific stage rather than include multiple stages.

    Hope these help!

  • Joan156
    Joan156 ✭✭

    Sadly, still getting syntax error using:

    =IF(OR([Document Stage]@row = "Hold", [Document Stage]@row=“[Lost/Canceled (Closed Lost)]”, [Document Stage]@row=“[Fully Executed (Closed Won)]”), 0, IF([Today's Date]@row < TODAY(-30), 1))

    I'll continue to test different variations

  • Joan156
    Joan156 ✭✭

    @ker9

    The Document Stage is a drop-down and I need to work what I have.

    I think you're right about the additional brackets. I removed them. We're close but now I have an UNPARSABLE message

    =IF(OR([Document Stage]@row = "Hold", [Document Stage]@row=“Lost/Canceled (Closed Lost)”, [Document Stage]@row=“Fully Executed (Closed Won]”), 0, IF([Today's Date]@row < TODAY(-30), 1))


    Thoughts?

  • ker9
    ker9 ✭✭✭✭✭✭

    @Joan156

    Your example above has a bracket instead of a paren after (Closed Won]

    =IF(OR([Document Stage]@row = "Hold", [Document Stage]@row=“Lost/Canceled (Closed Lost)”, [Document Stage]@row=“Fully Executed (Closed Won]”), 0, IF([Today's Date]@row < TODAY(-30), 1))


    =IF(OR([Document Stage]@row = "Hold", [Document Stage]@row = "Lost/Canceled (Closed Lost)", [Document Stage]@row = "Fully Executed (Closed Won)"), 0, IF([Today's Date]@row < TODAY(-30), 1, 0))

  • Joan156
    Joan156 ✭✭

    @ker9


    Great eye! It works perfectly!

    Thanks a million

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!