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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…