Formula to automate Status Column Dropdown Selection for Card View

jamiefico

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".

Kelly Moore

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

Kelly Moore

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

This worked beautifully, THANK YOU Kelly - much appreciated!