stop counting outstanding days when task is complete

Team Orbie 9

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

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.

Craig

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.

 

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.

Craig