Formula to stop Date Countdown

Seth Morth

Hi! See attached pic

CompletedSTOP.jpg

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

Paul Newcome

Are you currently capturing the date when the status changes to "Completed"?

Seth Morth

Hmmmm, no, I am not.

Is that even possible?

Paul Newcome

There are a few ways. My suggestion would be to insert a date type column and use a Record a Date automation to insert the date when the Status changes to "Completed". You could also have users manually enter the completed date. Regardless of how you capture the completed date, your formula will end up looking something like this...

=[Due Date]@row - IF([Completed Date]@row <> "", [Completed Date]@row, TODAY())

Seth Morth

Thank you Paul, this is great!

Paul Newcome

Happy to help. 👍️