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

Options
mjmitchell
mjmitchell ✭✭
edited 07/27/23 in Formulas and Functions

=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

  • mjmitchell
    mjmitchell ✭✭
    Answer ✓
    Options

    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

  • mjmitchell
    Options

    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
    Ramzi ✭✭✭✭✭
    Options

    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

    Smartsheet Solutions Architect

    www.adapture.com

  • mjmitchell
    Options

    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
    mjmitchell ✭✭
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!