Formula to Automate Stage

Andrea Mayer

Hello -

I am trying to figure out if there is a way to automate the selection of one of three stages (Not Started, In Progress, Completed) based on Start and Due date columns. 

Activities with a Start Date in the future would be 'Not Started', activities with a Due Date in the past would be 'Completed' and anything with a Start Date after today and Due Date in the future would be 'In Progress'. 

I've created the formula below but received and 'Unparseable' error.  As I was building the formula, I also received an 'Invalid Data Type' error - is this because they are not all text/number columns?  The Start and Due date columns are date columns and the Stage column is a dropdown column.

=IF([Start Date]@row, >=TODAY(), "Not Started", IF(AND([Due Date]@row, <=TODAY(), "Completed", "In Progress")))

Any help is appreciated. Thank you!

Andrea

Mike Wilday

Hi there, just a small modification will make that work for you. 

=IF([Start Date]@row>=TODAY(), "Not Started", IF([Due Date]@row, <=TODAY(), "Completed", "In Progress"))

This will automate your column. Be sure to lock it because if anybody manually changes the value then it will break the formula. 

Andrea Mayer

Thank you, Mike.  This works for 'Not Started' but does not work for the other status'.  Any thoughts on why?

Paul Newcome

Looks like Mike's fingers were feeling a little big yesterday. (@Mike - )

 

There is an extra comma after [Due Date]@row. Remove that, and it should be working just fine for you.

Mike Wilday

Yep. That and I was hopped up on caffeine. 

Mike Wilday

=IF([Start Date]@row>=TODAY(), "Not Started", IF([Due Date]@row<=TODAY(), "Completed", "In Progress"))

Paul Newcome

Haha. Story of my life.

Andrea Mayer

Perfect, thank you both!

Paul Newcome

Happy to help!