How do I get Working Days remaining to stop counting once a task is complete?

Paula D

working days.jpg

I'm using a SS template that has start/end date, working days remaining, duration, % complete and status. If I mark a task 100%, it automatically changes my status to "Complete". It's a nice little automation. What I'm noticing though is that the working days remaining field continues to count and I have a "-" (negative) number in that field. And the task was never overdue. I've taken a screen shot so you can see the formula SS has provided. I didn't modify it all. Can anyone assist me in correcting this? I mean I can delete the number from that field so it doesn't show a negative or anything but that's an extra step that I don't think I should have to do.

Paul Newcome

Give this one a try:

=IF([% Complete]@row = 1, 0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), ""))

Paul Newcome

Are you recording the date that you are marking the task as complete?

Paula D

I'm marking the percentage and the status as complete. If the end date is different than the one originally in the plan, then I'll change it, but most of the time, I consider the task complete by percentage and status columns.

Paul Newcome

Try this:

=MAX(0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), 0)

Paula D

Thank you for the information. This new formula is working and resetting the working days remaining to 0 for completed tasks, however, it has stopped counting to the negative if the task is overdue. Is there a way to keep the task counting until it's complete? If not, this new formula seems better than the other one. Thank you again!

Paul Newcome

Give this one a try:

=IF([% Complete]@row = 1, 0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), ""))

Paula D

Perfect! Thanks so much!

Paul Newcome

Happy to help. 👍️