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

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

Answers

  • Jeff M.
    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
    MSMITH2022 ✭✭✭
    edited 01/24/22

    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
    Jeff Reisman ✭✭✭✭✭✭

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff M.
    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
    MSMITH2022 ✭✭✭
    edited 01/27/22

    @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


  • Jeff M.
    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
    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.


  • Jeff M.
    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")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!