Status Column Formula Help

Hi all, I'm hoping someone can help me with a formula for the 'Status' column in my smartsheet.

I’d like to have the 'Status' based off of the '% Complete' column and refer to these percentages:

Status // Calculation

Past Due // <100% Complete after 'End Date'

Not Started // 0% Complete

In Progress // 0%> <99% Complete

Complete // 100% Complete

I'm having the most trouble with the Past Due formula. Please let me know if I need to provide more information.

Thank you in advance for any help you can offer!

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Nick Patil

    My apologies! I had my greater than/less than signs flipped. Try this:

    =if(AND([% complete]@row<1, [end date]@row<TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!