Automate [Actual Finish] based on when [% Complete] reaches 100%

Ben Donahue

I can't trust taskees to update [Actual Finish] reliably, so automation seems to be the key.

I want to automate [Actual Finish] based on when [% Complete] reaches 100%

I have really tried to bang my head through this, but to no avail. I keep running into endless updates of [Actual Finish] or #CIRCULAR REFERENCE issues.

Using a helper row provided no insight for me, either.

Thank you.

Mike Wilday

Have you considered using a system modified date column that would set the date that the row was last modified? Then use automation to lock the row when they set the task completion to 100%? 

The Modified System column will state the date that a row was last modified so if an admin changes anything on the row - the date would update. But if once a row is finished, you leave it be, then this might work for you. Locking the row will prevent editors from changing anything else on the row. 

Andrée Starå

Hi Ben,

To add to Mike's excellent suggestion you could also have an automatically saved backup as a PDF/ Excel file thru the Send as Attachment feature.

Would that work/help?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Another option if the previous ones don't work.

Zapier is a great option for this scenario. Is that an option for you?

Ben Donahue

You are not the first to suggest that I use Zapier. Tbh, I haven't looked into that functionality as of yet. I guess I will be doing that today.

Ben Donahue

Good suggestion, and seems simple enough to implement (famous last words, right?), but I have run into some issues with the Lock Row workflow, in that, I can't seem to get the rows to lock. I implemented the workflow, saved and refreshed, but to no avail. Attached are some screenshots. 

I added some conditional formatting that would use a white on white font and background if the % Complete cell is not 100%, to try to avoid confusion when a task is not actually finished. The down side is that all the Actual Finish cells are white/white, but that's not so bad.

Paul Newcome

The Lock Row workflow feature does take some time to kick in. I generally need to wait a few minutes or more before the row actually locks. 

 

I believe that it is subject to the same time frames as other workflows that send out emails in that it could take up to 15 minutes to send (lock) and then there is still the slight delay until the sheet refreshes with the automatic updates.

Andrée Starå

I can imagine! Let me know if you have questions!

Good luck! 

Ben Donahue

Oh, right. I see that now. Ty, Paul.

Mike Wilday

Zapier is an interesting tool. However, its 3rd party and to complete the task of populating a current date and time as a timestamp it will require a paid subscription to the service. 

Paul Newcome

Happy to help!