Automatically change status based on % complete and date

Lori Khoury

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.

1. If % Complete is 0% Status should be Not Started
2. If % Complete is blank Status should be blank
3. If % Complete is n/a Status should be Reference Only
4. If % Complete is 100% Status should be Complete
5. If % Complete is less than 75% and the Due Date is within 15 days Status is At Risk
6. If Due date is past today date and % Complete is not 100% then Status is Late
7. If % Complete is between 1% and 99% then Status is In Progress.

Brent Wilson

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

Lori Khoury

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

Brent Wilson

Yes @Lori Khoury you are correct.. I wrote this outside of Smartsheet in notepad.. I did forget the @Row