Record a date on the start date field when the status's changed

AAL

Hi Smartsheet Help Team,

When the status's changed to in progress, I want to set up the automation and record the date in the start date column and when it's completed, record the date in the end date column. However, it's said there are no date columns, but I have the start date and end date columns (see below). Do you know if there is any way I can do this other than automation?

Thanks in advance.

Matt Johnson

Hi @AAL

Are your date columns date type columns or text/number columns? Double click the column header to check the type and we'll from there.

Thanks, -Matt

AAL

Hi Matt,

My start date & end date columns are date/ time columns.

Thanks!

Paul Newcome

If you are using them in your dependency settings, you will not be able to populate them using the workflow. The message in the workflow is a little misleading. It should read that there are no useable date columns available.

AAL

So if I am using dependency settings, I can't use automation to do this. Then is there any other way I can set up this automatically by using a formula or any other way you can recommend? Thanks!

Paul Newcome

That is correct.

AAL

So is there any other way I can set up this automatically, such as using a formula or any other way you can recommend? Thanks!

Paul Newcome

Unfortunately no.

AAL

Thank you, Paul

Paul Newcome

Actually... There MIGHT be a way... How immediate do you need the update to be? Is it something where you mark it complete today and the date (today's date) is in there tomorrow when you log in?

AAL

When the status's changed to in progress, I want to record the date in the start date column and when it's completed, record the date in the end date column, that's it. Is it possible to make it automatically updated? 

Paul Newcome

It is not possible to make it immediate, but we can (potentially) have the sheet mark it automatically at the end of each day.

AAL

Well, can you explain it more about this?

Paul Newcome

Basically, we would use a record a date workflow on a second sheet that replicates the main sheet but without the dependencies turned on (since really we only care about recording the dates).

We would use a formula with cross sheet references to pull in the Status. We can't trigger an automation based on a cell with cross sheet references or cell links, but we can set it to run daily and have a condition of the Status being Complete (for end date). Since dependencies aren't turned on, we can populate the desired columns.

Then back in the main sheet, we should be able to use a cell link (not a formula) to link to the other sheet that contains the recorded dates.

So it wouldn't happen as soon as you mark it Complete, but it will run overnight and be there when you log in the next morning.