If Formula with Dropdown conditions based on Dates


I'm looking for a formula that could be integrated into a drop down field based on a past date.

I'm trying to have the "Status" column automatically flip to Off-Track if the date in the "Finish" column is now in the past and the "Status" column is not already in At Risk or Completed status.

I'm able to use =IF(Finish1 < TODAY(), "Off Track") but this is not taking in to account if the current "Status" column is not already in At Risk or Completed status. Does anyone know if there is a formula available for this?


Best Answer


  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Ryan Jared

    Use and AND clause to combine your conditions:

    =IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track") 

    I hope this helps,


    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Ryan Jared

    Thank you for that!

    If I could steal some more of your knowledge, one thing i'm struggling with is inputting the formula to the cell which has a drop down. Each row will start off as Not Started, and as the workflow goes on the status will manually change.

    How can I pre-set the cell to have the formula and still stay active as the drop down items change.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!