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
-
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!
-
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))
-
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!
-
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
-
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?
-
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))
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!