[% Complete] interaction with [Actual Finish Date]

djpreece

Hi all,

We would like [Actual Finish Date] be set to the date that [% Compete] is set to 100%

I thought this would  be straight forward:

[Actual Finish Date]

     =IF([% Complete] = 1, [Last Entry Date], "")

<[Last Entry Date]  is a “Auto-Number/System” column type with the value Modified(Date).>

Problem is [Last Entry Date] is set by the system ANY Time ANY CELL in the row is changed

ie: any changes to the row after [%Complete] is set to 100% will cause [Actual Finish Date] to change

Is there a way to lock the date in [Actual Finish Date] to the date [% Complete] is set to 100%? 

Thanks

Darrell

Chris McKay

Nope. The Created and Modified system date/time columns are locked and you have no control over what goes into them. Which means you can't stop them from calculating new values once they've got their claws in, apart from deleting the whole column.

Not only that, but the underlying date/time is UTC, no matter where you are. It display the timezone specified in your locale, but it's meaningless. I'm in Australia (UTC +10 or +11), so I often have situations where I have system dates showing yesterdays's date at 10am here. Not the best situation for a PPM I know.

Not much more I can say other than to join the chorus of voices telling them to sort it out.

Sorry to disappoint.

Kind regards,

Chris McKay

Mike Wilday

Sing it out. We need some kind of way to timestamp smartsheets when an event occurs. 

Shaine Greenwood

Hi Darrell,

You might consider changing % complete to 100% the last part of your workflow for a task.

You can even create an auto-lock row rule to ensure that nothing else on the row is changed after the row is set to 100%

More on the auto-lock rule is available in the help center: https://help.smartsheet.com/articles/2476686-lock-rows-automatically-to-preserve-data

 

Chris McKay

Hi Shaine,

The lock feature is nice, if you want to lock the whole row. But if the workflow doesn't permit this then he is back to square one.

I implore you. Beg even. Please reiterate to your Product Team (although I have via other channels) the frequency that requests are made here for certain features like this. Why they insist on prioritising functionality that doesn't matter to anyone (e.g. Facebook Chat) while we are paying to use a PPM tool that can't do dates worth squat, can't assign multiple resources, can't do baselines and has a template deployment/workflow that still needs loads of work.

Kind regards,

Chris McKay

Joseph Bartlow

The only way I have found to set a completion date that won't change is with archiving.

So I set automation that when %complete reaches 100% -> copy to a dummy sheet. You will have a hidden column on your sheet that houses the formula with the Today() function to calculate the date.

When 100% is reached a date is generated then copied to the dummy/archived sheet.

Lastly your actual completion date uses a vlookup to pull back the archived date. Have the column locked. It won't changed again.

Best,