Formula to automate Status Column Dropdown Selection for Card View

Options

Hello, I'd like to automate which value is selected from the dropdown list in my STATUS column on my project plan sheet, so that I can use the card view without manual assignment beforehand of the status column. Is this possible to do a formula on a dropdown column? Or, should I be setting up an automation instead?

Here's what I'm trying to do: if the row is less than 100% for the % complete field and the end date is prior to today's date, choose "Past Due" for the status. If it's < 100% but the end date is in the next 5 days choose "Due This Week", and if < 100% with end date in the next 10 days choose "Due Next Week".

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 04/26/23 Answer ✓
    Options

    Hey @jamiefico

    try this. I did add a condition if the [% complete] = 1. You might need to change the word 'Complete' to what it should say when 100% is reached.

    =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Past Due", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), [End Date]@row <= TODAY(5)), "Due This Week", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), [End Date]@row <= TODAY(10)), "Due Next Week"))))

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 04/26/23 Answer ✓
    Options

    Hey @jamiefico

    try this. I did add a condition if the [% complete] = 1. You might need to change the word 'Complete' to what it should say when 100% is reached.

    =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Past Due", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), [End Date]@row <= TODAY(5)), "Due This Week", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), [End Date]@row <= TODAY(10)), "Due Next Week"))))

    Will this work for you?

    Kelly

  • jamiefico
    Options

    This worked beautifully, THANK YOU Kelly - much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!