Record a date on the start date field when the status's changed
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.
Answers
-
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
-
Hi Matt,
My start date & end date columns are date/ time columns.
Thanks!
-
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.
-
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!
-
That is correct.
-
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!
-
Unfortunately no.
-
Thank you, Paul
-
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?
-
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?
-
It is not possible to make it immediate, but we can (potentially) have the sheet mark it automatically at the end of each day.
-
Well, can you explain it more about this?
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives