Formula to Automate Stage

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

Find more posts tagged with

Hospitality

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…