How do I get Working Days remaining to stop counting once a task is complete?
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.
Best Answer
-
Give this one a try:
=IF([% Complete]@row = 1, 0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), ""))
Answers
-
Are you recording the date that you are marking the task as complete?
-
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.
-
Try this:
=MAX(0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), 0)
-
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!
-
Give this one a try:
=IF([% Complete]@row = 1, 0, IFERROR(NETWORKDAYS(TODAY(), [End Date]@row), ""))
-
Perfect! Thanks so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!