Automatically change status based on % complete and date
Hi
I was wondering if someone could help me put together a formula to automatically change status
I am trying to setup the following and keep getting errors.
 If % Complete is 0% Status should be Not Started
 If % Complete is blank Status should be blank
 If % Complete is n/a Status should be Reference Only
 If % Complete is 100% Status should be Complete
 If % Complete is less than 75% and the Due Date is within 15 days Status is At Risk
 If Due date is past today date and % Complete is not 100% then Status is Late
 If % Complete is between 1% and 99% then Status is In Progress.
Answers

Try this
=IF([% Complete]@row=0, "Not Started", IF(ISNULL([% Complete]@row), "", IF([% Complete]@row="n/a", "Reference Only", IF([% Complete]@row=1, "Complete", IF(AND([% Complete]@row<.75,[Due Date]Today()<15), "At Risk", IF(AND(NOT([% Complete]@row=1),[Due Date]Today()<0), "Late", "In Progress"))))))
I did not test it so lets hope my brian is working
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Hi Brent  I am not sure why but I got unparseable. Should I have @row after [Due Date]?

Yes @Lori Khoury you are correct.. I wrote this outside of Smartsheet in notepad.. I did forget the @Row
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Help Article Resources
Categories
Check out the Formula Handbook template!