stop counting outstanding days when task is complete

Team Orbie 9
Team Orbie 9 ✭✭
edited 12/09/19 in Formulas and Functions

Hello All,

I am using this formula =TODAY() - [Date Field Checked]3 but I need to stop this count when the item is complete.

Could someone please help out?



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    How does the user signify complete? Check box, status text, or % Complete.

    By stop the count, do you want it to return blank when complete or keep the last value?

    Smartsheet does not do the second one -- I use Zapier for that if I have to. Or the API.

    If just blank then

    Assuming you have a checkbox in a column name Done

    =IF(Done@row,"",TODAY() - [Date Field Checked]@row)

    I hope this helps.


  • Team Orbie 9

    Hi Craig,

    Thank you. I would like it to just store the value. They check "Partial- holding for rework" or "complete". As soon as complete is checked I just want it to hold.


    Completion Example.jpg

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Smartsheet won't do that.

    It needs a toggle function or a set-reset function, which is does not have.

    If this is a single sheet or a small fixed set of sheets, I would use Zapier.

    What you are looking for an accumulator, not a counter. Now that I think about it, the API might be easier to implement such a thing.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!