Date & Timestamp a specific cell change

MBMaddox

Hello,

I am very new to Smartsheet and I am working on a project tracking sheet. We have the following statuses in a cell: Unassigned; In Progress; Completed; Cancelled; On Hold

当提交一个请求自动三ggers a 'Created Date' column that logs the date/time of creation. I would like this to be duplicated to two other columns I have 'Modified Date' and 'Completed Date'.

When the status in a cell changes to In Progress or On Hold, I would like the 'Modified Date' cell on that row to log the date and time.

When the status in a cell changes to Completed or Cancelled, I would like the 'Completed Date' cell on that row to log the date and time.

Could you offer step by step instruction or point me to a video that shows how to accomplish this, if possible.

Thank you so much for your help and sorry if I've explained poorly.

Toufong Vang

@MBMaddox

Hi, here is another approach.

Let's say that your sheet is calledMy Sheet. Ensure that this sheet has the system column "Modified Date". In this sheet:

1) Create a Text/Number column named "Timestamp - Status In-Progress or On-Hold" (or something more preferrable).

2) Create a Text/Number column named "Completed Date".

3) Create a Text/Number column named "Last Status" (helper column)*.

4) Create a Text/Number column named "OK to Route?" (helper column) and place the column formula=IF([Last Status]@row =[email protected], 0, 1)

5) Create a new sheet that has only the "Primary Column". Let's name this sheetMy Sheet - Record Dates(or something more preferrable).

6) Copy one row from "My Sheet" to "My Sheet - Record Dates". (Do this by left-clicking on the row number and select "Copy to Another Sheet..." from the options.)

7) Create a workflow automation configured as follows:

Trigger: When rows are changed...When Status changes to Any Value

Condition: When OK to Route? is equal to 1.

Action: Move rows...Move to "My Sheet - Record Date"

Open the sheetMy Sheet - Record Dates.

8) In the column "Timestamp - Status Changed to In-Progress or On-Hold", create the column formula= IF( OR([email protected]="In Progress",[email protected]="On HOld"), [Modified Date]@row+"", "")

9) In the column "Completed Date" create the column formula= IF( OR([email protected]="Completed",[email protected]="Canceled"), [Modified Date]@row+"", "")

10) In the column "Last Status" create the column formula[email protected]

11) Create a workflow automation configured as follows:

Trigger: When rows are added or changed...When Primary Column changes to to Any Value

Conditions: Where "Timestamp - Status In-Progress or On-Hold" is not blank OR where "Completed Date" is not blank.

Action: Move rows...Move to "My Sheet"

Done!

Here's how this works.

At the time the row is created in My Sheet,Last Statusis blank andOK to Routeis 1.

WhenStatusis changed and saved,Modified Dateis recorded and automation moves the row into My Sheet - Record Date.

Depending on whatStatusis equal to, theModified Datewill be recorded inTimestamp - ...orCompleted Date. The formula inLast Statussets the value equal to whatever was recorded inStatus. Because the trigger was set to "When rows are added or changed" automation moves the recently added row back to My Sheet.

Now back in My Sheet,StatusandLast Statusare the same soOK to Route?is 0 (or False). This fails the condition for automation to move the row until someone changes the value inStatus.

_______________

*The helper columns prevent an infinite loop from occurring when automation moves a row back from "My Sheet - Record Date".

Hollie Green

Have you tried a sheet automation for record a date there is a template in the automation.

Andrée Starå

Hi@MBMaddox

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by <\/em>marking it as helpful<\/em><\/strong>. It will make it easier for others to find a solution.<\/em><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\nI hope you're well and safe!\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\nLet me know if you have any questions!\\nStay safe and have a fantastic day!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/home\\\/leaving?allowTrusted=1&target=https%3A%2F%2Fworkbold.com%2F\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n✅\"},{\"attributes\":{\"italic\":true},\"insert\":\"Did my post(s) help or answer your question or solve your problem? Please help the Community by \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"marking it as helpful\"},{\"attributes\":{\"italic\":true},\"insert\":\". It will make it easier for others to find a solution.\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-08T18:36:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅我的帖子(s)帮助或吗answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MBMaddox

@Andrée Staråthank you very much for your post. I apologize, I'm still quite new to this, so I'm not familiar with "copy-row automation and a VLOOKUP or combination of INDEX/MATCH". Do you happen to know of a help video or step by step guide that I could reference? Thank you so much!

Toufong Vang

@MBMaddox

Hi, here is another approach.

Let's say that your sheet is calledMy Sheet. Ensure that this sheet has the system column "Modified Date". In this sheet:

1) Create a Text/Number column named "Timestamp - Status In-Progress or On-Hold" (or something more preferrable).

2) Create a Text/Number column named "Completed Date".

3) Create a Text/Number column named "Last Status" (helper column)*.

4) Create a Text/Number column named "OK to Route?" (helper column) and place the column formula=IF([Last Status]@row =[email protected], 0, 1)

5) Create a new sheet that has only the "Primary Column". Let's name this sheetMy Sheet - Record Dates(or something more preferrable).

6) Copy one row from "My Sheet" to "My Sheet - Record Dates". (Do this by left-clicking on the row number and select "Copy to Another Sheet..." from the options.)

7) Create a workflow automation configured as follows:

Trigger: When rows are changed...When Status changes to Any Value

Condition: When OK to Route? is equal to 1.

Action: Move rows...Move to "My Sheet - Record Date"

Open the sheetMy Sheet - Record Dates.

8) In the column "Timestamp - Status Changed to In-Progress or On-Hold", create the column formula= IF( OR([email protected]="In Progress",[email protected]="On HOld"), [Modified Date]@row+"", "")

9) In the column "Completed Date" create the column formula= IF( OR([email protected]="Completed",[email protected]="Canceled"), [Modified Date]@row+"", "")

10) In the column "Last Status" create the column formula[email protected]

11) Create a workflow automation configured as follows:

Trigger: When rows are added or changed...When Primary Column changes to to Any Value

Conditions: Where "Timestamp - Status In-Progress or On-Hold" is not blank OR where "Completed Date" is not blank.

Action: Move rows...Move to "My Sheet"

Done!

Here's how this works.

At the time the row is created in My Sheet,Last Statusis blank andOK to Routeis 1.

WhenStatusis changed and saved,Modified Dateis recorded and automation moves the row into My Sheet - Record Date.

Depending on whatStatusis equal to, theModified Datewill be recorded inTimestamp - ...orCompleted Date. The formula inLast Statussets the value equal to whatever was recorded inStatus. Because the trigger was set to "When rows are added or changed" automation moves the recently added row back to My Sheet.

Now back in My Sheet,StatusandLast Statusare the same soOK to Route?is 0 (or False). This fails the condition for automation to move the row until someone changes the value inStatus.

_______________

*The helper columns prevent an infinite loop from occurring when automation moves a row back from "My Sheet - Record Date".

MBMaddox

@Toufong VangWOW! Thank you so much for your very thorough and helpful answer. You are amazing!

Ali412

@Toufong VangThank you for the nice workaround!

I noticed that the returned rows are sent to the bottom of the sheet.

Is it possible to return the rows to the same location they were taken from?

Andrée Starå

Hi@Ali412

I hope you're well and safe!

It's not possible with Toufong's excellent method, but you can do it with mine because it collects the value back to the same row.

More info:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by <\/em>marking it as helpful<\/em><\/strong>. It will make it easier for others to find a solution.<\/em><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\nI hope you're well and safe!\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\nLet me know if you have any questions!\\nStay safe and have a fantastic day!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/home\\\/leaving?allowTrusted=1&target=https%3A%2F%2Fworkbold.com%2F\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n✅\"},{\"attributes\":{\"italic\":true},\"insert\":\"Did my post(s) help or answer your question or solve your problem? Please help the Community by \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"marking it as helpful\"},{\"attributes\":{\"italic\":true},\"insert\":\". It will make it easier for others to find a solution.\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-08T18:36:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅我的帖子(s)帮助或吗answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ali412

@Andrée StaråThanks Andre!

My issue is that I still do not have a unique ID per each row.

However, I realized that I don't really need accurate timestamp that much, so I might just settle for the workflow template of recording the date.

Andrée Starå

@Ali412

Happy to help!

You can add an Autonumbering column and have the unique ID you need.

Make sense?

✅Remember!我的帖子(s)帮助或吗answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Toufong Vang

Hi,@Ali412, if you don't already have an auto-number column, then:

1) Sort the sheet in the order that you would like.

2) Create an auto-number column.

3) Sort the sheet on the auto-number column.

You can avoid having to perform the sort each time there is a new row if you use@Andrée Starå's "VLOOKUP/INDEX/MATCH" method.