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
Check out the Formula Handbook template!