Automate Status Based on Due Date and Complete Date

I have a status column that uses the Red, Yellow, Green, and Blue symbols. I would like it to automatically update based on the dates in the Due Date and Compete Date columns. The parameters are as follows:

If there is a Complete Date = Green

If there is no Complete Date, AND Due Date is over 7 days away = Blue

If there is no Complete Date, AND Due Date is less than 7 days away = Yellow

If there is no Complete Date, AND Due Date is past today = Red

This is the formula I've tried, but I can't get it to work.

=IF(NOT(ISBLANK([Complete Date]@row)), "Green", IF(AND([Due Date]@row > TODAY(-7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(-7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row), “Red"))))

Any suggestions?

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey Amy

    Let's try this

    =IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row >=TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row <=TODAY(7), [Due Date]@row>=TODAY(), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

    Did this fix it?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Amy V

    Does this work for you?

    =IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

    Kelly

  • Amy V
    Amy V ✭✭

    @Kelly Moore, thank you for your help!

    That works better—I am not getting the “#UNPARSEABLE” error now. I discovered that when the Due Date was exactly 7 days away, no color was returned, so I adjusted the date for the yellow calculation to add 8 instead of 7. This is what I have currently.

    =IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(8), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

    The last IF statement (Red) is the only thing not working now. No matter how old of a date I put in the Due Date column, the status stays yellow (when the Complete Date is blank). I’m not sure why it won’t change red.

    Thank you so much for your help and being willing to share your time and expertise!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey Amy

    Let's try this

    =IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row >=TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row <=TODAY(7), [Due Date]@row>=TODAY(), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

    Did this fix it?

    Kelly

  • Amy V
    Amy V ✭✭

    @Kelly Moore,

    This works great! I've run a variety of scenarios and they all work as they should. Thank you so much for your help!

    Amy

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Anytime. Glad it works

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!