Change "End Date" based on status change?

Amber Eakin

Hello, all!

Is it possible to change the "End Date" column based on when someone completes a task?

Sometimes my team finishes a task on time. Sometimes it's a few days late. Sometimes (like today), a person finishes nearly a week early! Of course, if the end date is off, that messes with all successor tasks. We're doing a significant review of task/resource management this quarter, so these have to be accurate.

Currently, I have an automation to let me know if someone marks an item as "complete" and the end date is not today. I tried to set up an automation to change the cell value, but the date columns aren't an option.

Is there any hope for me that isn't a manual process?

Thank you!

iamje

Yes, there's an automation that you can use. Whenever the task status changed to complete the end date will be recorded automatically. You can also add additional criteria if you want to.

Amber Eakin

Hi @iamje - I know I can set up an automation to automatically log a date in a system column, but that's not what I'm after. Do you know how to do it for an existing date column?

Paul McGuinness

Hi @Amber Eakin

You can work around this by using a couple of helper columns, one would be planned end date and the other actual end date, you would then use a formula in the end date column to reference the planned date column until the task is marked complete and then use the automation to populate the actual end date column at which point the formula would then read the true date.

Something like this

Formula in the End date. column is =IF(ISBLANK([Actual end date]@row), [Planned end date]@row, [Actual end date]@row)

Hope that helps

Thanks

Paul

Amber Eakin

@Paul McGuinness - This is elegant, but I can't get it to work, either. I can't use formulas in the column that is part of my dependency settings.

I might have to simply set up an automation to send me an update request any time a task is marked as completed if the "End Date" is not today. Not ideal, but quicker than going into the sheet constantly.

Paul McGuinness

Hi @Amber Eakin

Sorry it didn't work in the context you needed.

Hoping you can get to a more practical solution that works with the dependencies.

Thanks

Paul

Ksephora

Were you ever successful in figuring this out because I'd love to know too. My users don't want to have to change a status to complete and update the end date. But you're right that if they don't, it throws off the dependencies.

Amber Eakin

Regrettably, @Ksephora , nothing elegant. I set up an automation to email me every time someone changes a status to "Complete." If the end date isn't the day of the email, I manually change it.

That wouldn't work for a large team or something with several updates a day, but it works for me...at least until Smartsheet develops something better!

Paul Newcome

Unfortunately that is one of the limitations within Smartsheet. Formulas and automations cannot be used in date type columns being used in your dependency settings.

Feel free to browse the Product Ideas tab to see if someone has already submitted this idea. If they have, you can add your vote. If they have not, you can submit the idea yourself.

Either way, please provide a link to the Idea so that others searching for the same thing can easily add their votes.

Amber Eakin

Good idea, @Paul Newcome ! I've submitted an idea for this issue: https://community.smartsheet.com/discussion/110498/change-end-date-based-on-status-changes/p1?new=1. Tagging @Ksephora in case you would also like to vote on it. Thank you both!

Haissam

I would also love to see this possibility happening in the Smartsheet. I also need to monitor the Planned (Baseline) vs Actual completion date as on the Task Completion date. Let see how long it would take to be realized.