Formula for Task Status using dropdown and Start | End dates


I'm trying to get a formula to work and I can't figure out what I've got wrong. Any help would be appreciated

I would like to update a Task status dropdown field (Not Started, In Progress, Completed, Past Due)

My formula is:

IF(Completed@row = 1, "Completed", IF(AND([Start Date]@row <= [TODAY Helper]@row, [End Date (Due Date)]@row > [TODAY Helper]@row), "In Progress", IF(AND([Start Date]@row < [TODAY Helper]@row, [End Date (Due Date)]@row < [TODAY Helper]@row, Completed@row = 0), "Past Due", "Not Started")))

It works if the Completed column is checked but otherwise I get an #INVALID OPERATION error message.

This is what I'm trying to do:

Completed is checked, "Completed"

Start Date less than or equal to Today Helper AND End Date (Due Date) is > Today Helper, "In Progress"

Start Date less than Today Helper AND End Date (Due Date) < Today Helper AND Completed unchecked, "Past Due"

Otherwise it should be "Not Started"

Thank for any help!


Best Answer

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓

    @Peggy Parchert

    Peggy, I am getting your formula to work exactly as written, in a text based column type, changing that column does not seem to change the results, but wanted to let you know. Have you confirmed that all date columns are set to Date functionality?

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!