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.

我可能要简单et 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