Formula to stop Date Countdown

Hi! See attached pic

I have struggled with this formula and not had any success to get it to do what I want.

Current formula is:

=[DUE DATE]@row - TODAY()

...this gives a handy countdown.

In the pic you can see the first line should not still be counting down as the task was completed 12 days ago.

What I need it to do is stop counting the result when the 'STATUS' column changes to "Completed". Ideally, and this haunts me as well, I want to freeze the 'DAYS LEFT' when the status changes to "Completed", not simply turn to Zero.

I think I should be using =IF but the text semantics continue to allude me.

Any and all help appreciated!

  • Seth


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!