I am trying to return where a contract is in the review process, DOJ review may or may not have date
=IF(Started@row > [To OC&P]@row, "HSD Contracts", IF([To OC&P]@row > DOJ@row, "OC&P", IF(OR(DOJ@row > [To Contractor]@row, "DOJ", IF(DOJ@row = 0, IF([To Contractor]@row > [To OC&P]@row, "Out for Signature"), IF([To Contractor]@row > [HSD Signed]@row, "Out for Signature", ""))))))
Best Answer
-
This took me a few hours to finally figure out and clearly, I have much to learn related to SmartSheet function rules. This is the final formula that worked. Sharing in case others may have a similar need.
=IF(AND(ISBLANK(DOJ@row), ISDATE([To Contractor]@row)), "Out for Signature", IF(Started@row > [To OC&P]@row, "HSD Contracts", IF([To OC&P]@row > DOJ@row, "OC&P", IF(DOJ@row > [To Contractor]@row, "DOJ", IF([To Contractor]@row > [HSD Signed]@row, "Out for Signature", IF(DOJ@row <> [To Contractor]@row, "Out for Signature"))))))
Once tested, this can be converted to column formula.
Answers
-
Not sure what the trick is for this hoping someone might be able to help. This is a progressive review process, with one complex part. The DOJ review may or may not get a date and I need it to look to To Contractor instead and return "Out for Signature".
-
Not sure what you're trying to do exactly, but the ISDATE function my be useful in this case?
Smartsheet Solutions Architect
www.adapture.com
-
Thanks for the suggestion. I have columns labeled: Started, OC&P, DOJ, To Contractor. Each are a review stage. A date will be entered in the corresponding row when the review was completed. I am creating a column that will logically identify what review stage the agreement is in. I did try to use the ISDATE as below, however, I keep getting unparseable.
Example: Started - Desired response "HSD Contracts"
Example: To OC&P - Desired response - "OC&P"
Example: To DOJ - Desired response - "DOJ" (Note - This is the tricky part - DOJ will not always have a date and will need to have alternate response - " Out for Signature"
Example - This is the tricky part - DOJ will not always have a date and will need to have alternate response - " Out for Signature"
Example: To Contractor - Desired response "Out for Signature"
=IF(OR(ISBLANK([To OC&P]@row), ISDATE(Started@row)), "HSD Contracts", IF(OR(ISBLANK(DOJ@row), ISDATE([To OC&P]@row)), "OC&P", IF(OR(ISBLANK([To Contractor]@row),ISDATE(DOJ@row)), "DOJ",IF(OR(ISBLANK(DOJ@row),ISDATE([To Contractor]@row,"Out for Signature"))))))
I am new to the rules for Smartsheet formulas for sure, not a great deal of resources online for this particular type of formula. Any help or guidance would be greatly appreciated.
-
This took me a few hours to finally figure out and clearly, I have much to learn related to SmartSheet function rules. This is the final formula that worked. Sharing in case others may have a similar need.
=IF(AND(ISBLANK(DOJ@row), ISDATE([To Contractor]@row)), "Out for Signature", IF(Started@row > [To OC&P]@row, "HSD Contracts", IF([To OC&P]@row > DOJ@row, "OC&P", IF(DOJ@row > [To Contractor]@row, "DOJ", IF([To Contractor]@row > [HSD Signed]@row, "Out for Signature", IF(DOJ@row <> [To Contractor]@row, "Out for Signature"))))))
Once tested, this can be converted to column formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!