I am trying to return where a contract is in the review process, DOJ review may or may not have date
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
Check out the Formula Handbook template!