I am trying to return where a contract is in the review process, DOJ review may or may not have date

mjmitchell

=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", ""))))))

mjmitchell

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.

mjmitchell

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".

Ramzi

Not sure what you're trying to do exactly, but the ISDATE function my be useful in this case?

https://help.smartsheet.com/function/isdate

mjmitchell

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.

mjmitchell

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.