How do you automate a status updates in smartsheet with IF formula?

MSMITH2022

My organization would like to automate the statuses in a project plan. Could we create an IF formula to accommodate this logic?

Examples:

If the start date is in the past and the percentage is 0%, put status in yellow

If the percentage is 100%, put the status in green

If the end date is past due, put the status in Red

Jeff M.

You could use conditional formatting to automate this process and highlight the cells different colors, or you could create a symbol column with status lights and use IF statements to change the color of the symbol.

MSMITH2022

Thank you Jeff,

Could you kindly provide an example formula? We are trying to automate the status column (Symbol) of each task if they fall under the rule highlighted above and not let the PM do so.

Jeff Reisman

https://community.smartsheet.com/discussion/87054/how-do-you-automate-a-status-updates-in-smartsheet-with-if-formula

The problem you're going to have here is that you're mixing requirements, and therefore will have unreliable results with a single status column.

The first step would be to clean up the logic of what you want to show with a status column:

Do you want to show a status based on percent complete?

Do you want to show a status based on if a project has gone past it's expected completion date?

Do you want to show status based on percent complete AND some date relative to another date?

Once you fully flesh out your rules, you can decide if one status column is sufficient, if you need more than one, and if you require some hidden helper columns to do some of the work behind the scenes.

Jeff M.

I have put together a formula that will update your symbol column using status Red, Yellow, Green. Make sure you update the column names to match your sheet columns. Let me know if you have any issues.

=IF(AND([End Date]@row < TODAY(), [End Date]@row <> "", Percent@row < 100), "Red", IF(AND([Start Date]@row < TODAY(), [Start Date]@row <> "", Percent@row = 0), "Yellow", IF(AND([Start Date]@row <> "", Percent@row = 100), "Green")))

MSMITH2022

@jeff, you are such a life saver. I want to thank you for your help..

So, I inserted the formula above and got #Unparseable error..

I've attached the column names and the formula for you to reference. What am I doing wrong? I've update the column names .. Forgot to add, the screen shot starts at line 33

update....PNG

Jeff M.

Hi,

It looks like you still need to update the column name in the formula to match, try " [% Complete]@row " instead of percent@row.

MSMITH2022

Thank you, Jeff

Again, I want to thank you for taking the time to help him out on this matter. I'm still learning :-)

The "[% Complete]@row" absolutely worked with the formula you provided above.

However, the status icon is turning red when the task is completed. Do I need to add another statement logic to make it turn green if the percentage is 100%? I have a screenshot of what I'm referring to.

The project Assessment tasks were completed in November at 100% but the status icon is Red- Can we turn those green when the task is completed. Our CIO wants to logic to be based on the dates and not progress.

snapshot.PNG

Jeff M.

You're welcome 🙂

So I guess when dealing with a percent column the formula needs to have decimal numbers. I made a few changes to the numbers and it appears to be working correctly when using a percent.

Let me know if I can help you with anything else!

=IF(AND([End Date]@row < TODAY(), [End Date]@row <> "", Percent@row < 1), "Red", IF(AND([Start Date]@row < TODAY(), [Start Date]@row <> "", Percent@row = 0), "Yellow", IF(AND([Start Date]@row <> "", Percent@row = 1), "Green")))