Fill in Status based on multiple combinations



I've been trying to figure out how to automatically populate a column in a column, based on data in 2 other columns.

I want the status to populate "Open" or "Closed" based on the information in the Closure Date and Opening Date columns.

Blank Closure Date and Blank Opening Date = "Open"

Closure Date Present, Opening Date Blank= "Closed"

Closure Date Present, Opening Date Present = "Open"

I've tried several If / And formula's but haven't been able to crack them.



  • Adrian Backus
    Adrian Backus ✭✭✭✭

    Try this:

    =if((isblank([Closure Date]@row), if((isblank([Opening Date]@row),"Open","Invalid Entry"), if((isblank([Opening Date]@row),"Closed","Open"))

    I assumed that blank closing and present opening dates are invalid entries because that wasn't defined in your question, but you could change that to whatever you want.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!