How can I log a timestamp of a cell change?

Bishoy Mansour

I want to add an automation rule to write a timestamp of when a cell value changes, I know I can use the "record a date" automation to get the date but is there any way I can get the exact timestamp similar to the "created at" field in form responses?

I am also looking into a way to implement it using the API and there seems to be no way around it.

Any luck, everyone?

Paul Newcome

You should be able to grab it via API.

Another option would be to insert an auto-number column and a Modified (date) column. Then you would set up a Copy Row automation to trigger when that particular cell changes. Finally you would use an INDEX/MATCH formula to match on the auto-number column and pull the date/timestamp from the copy sheet.

Andrée Starå

Hi@Bishoy Mansour

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å<\/p>

Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\n\\nI hope you're well and safe!\\n\\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!\\n\\nLet me know if you have any questions!\\n\\nStay safe and have a fantastic day!\\n\\nBest,\\n\\nAndrée Starå\\n\\nWorkflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"insert\":\"WORK BOLD\"},{\"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-07-01T17:02:07+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

• You can absolutely do it with the API as well.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

✅我的订单st(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bishoy Mansour

You should be able to grab it via API.<\/p>

Another option would be to insert an auto-number column and a Modified (date) column. Then you would set up a Copy Row automation to trigger when that particular cell changes. Finally you would use an INDEX\/MATCH formula to match on the auto-number column and pull the date\/timestamp from the copy sheet.<\/p>","bodyRaw":"[{\"insert\":\"You should be able to grab it via API.\\n\\nAnother option would be to insert an auto-number column and a Modified (date) column. Then you would set up a Copy Row automation to trigger when that particular cell changes. Finally you would use an INDEX\\\/MATCH formula to match on the auto-number column and pull the date\\\/timestamp from the copy sheet.\\n\"}]","format":"rich","dateInserted":"2021-08-20T19:16:46+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-30T20:24:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/302231#Comment_302231","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/302231#Comment_302231

Hi @Bishoy Mansour<\/a> <\/p>

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

Please have a look at my post below with a method I developed.<\/p>

More info: <\/p>

https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier<\/a><\/p>

• You can absolutely do it with the API as well.<\/li><\/ul>

  Would that work\/help? <\/p>

  I hope that helps!<\/p>

  Be safe and have a fantastic weekend!<\/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 support the Community by <\/em>marking it Insightful\/Vote Up or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Bishoy Mansour\",\"userID\":139184}}},{\"insert\":\" \\nI hope you're well and safe!\\nPlease have a look at my post below with a method I developed.\\nMore info: \\n\"},{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/68589\\\/lock-or-store-date-value-solution-without-using-zapier\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/68589\\\/lock-or-store-date-value-solution-without-using-zapier\"}},{\"insert\":\"\\n\"},{\"insert\":\"You can absolutely do it with the API as well.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Would that work\\\/help? \\nI hope that helps!\\nBe safe and have a fantastic weekend!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n✅\"},{\"attributes\":{\"italic\":true},\"insert\":\"Did my post(s) help or answer your question or solve your problem? Please support the Community by \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"marking it Insightful\\\/Vote Up or\\\/and as the accepted answer\"},{\"attributes\":{\"italic\":true},\"insert\":\". It will make it easier for others to find a solution or help to answer!\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2021-08-20T19:21:46+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-07-01T17:02:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/302239#Comment_302239","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/302239#Comment_302239

The only problem to your solutions is that my original copied row already has a Created (Date) column, when i copy it over it also copies the created (date) column and i cannot have two created columns. I am working on an IT ticketing system and am trying to get an open and closed ticket times. any other way i can implement this?

ShaunW

I know its a bit late to this thread but you can do this via a workflow so i used a "trigger event" so when a task was marked as "complete" i set a condition to record a date in a specific cell, hope that helps

BethWork

@ShaunWHello there! I'm trying to do the same thing as OP. Do you have exact steps for how you set that up? It sounds like you created an automation and a formula in your sheet. I, too, want to track the start time when something begins and then the stop time when it ends.

ShaunW

嗨@BethWorkso i just used this automation, hope this helps. Shaun

Andrée Starå

Hi@BethWork

I hope you're well and safe!

If you need the time too.

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-07-01T17:02:07+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

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅我的订单st(s) help or 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!

BethWork

@Andrée StaråI read through your previous response and I'm almost following.

Set up a single sheet where I can copy rows and include the created date/time column.

1. Set up copy row automation that would triggered based off status of "In Progress"
2. When it's in progress that will time stamp on the "helper sheet" with the copied row
3. When the task is set to status "Completed" on my target sheet it will copy that row to my "helper sheet"
4. Do I create a formula on my helper sheet to calculate the total time from start to finish?

If I'm understanding it correctly, I'm not sure entirely what to do at step #4.

BethWork

@Paul NewcomeI was trying to follow along your thread herehttps://community.smartsheet.com/discussion/79654/how-to-input-a-date-time-and-calculate-a-time-elapsedbecause I'm try to solve a similar problem. I've partly followed the steps above.

I've used a copy row automation onto a helper sheet based on a trigger from the source sheet. When it's copied over that sheet stamps it with a created date/time column.

Once the action is completed, it copies the row onto my helper sheet a second time and stamps it with the created date/time column again. That means I'll essentially have duplicate entries with the only difference being the created date column. I want to then calculate how much time elapsed between the start and finish.

For instance, if it time stamps at 8:30AM and time stamps at 11:00AM, I would expect to have it say 1.5hrs (or just 1.5). How would I accomplish that formula?

Paul Newcome

You would use a MIN/COLLECT to pull in the first date/time based on the unique id and a MAX/COLLECT to pull in the second date/time.

Then you should be able to find something in the below thread that will help you calculate the time difference.

I have decided to create a post that consolidates all of the different time based solutions that I have put together.<\/p>

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.<\/p>

I thought it would be helpful to have them all in one place instead of spread out.<\/p>

I welcome all feedback, suggestions, and additions.<\/p>

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.<\/p>

I will also try to post various tips and tricks as I think of them as well such as:<\/p>

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:<\/p>

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))<\/p>

Calculating Time Worked for Employees<\/a><\/p>

Can you calculate time in Smartsheet?<\/a><\/p>

Conversion of timezones (Solved Formula Included)<\/a><\/p>

Date\/Time\/Year<\/a><\/p>

Flagging a Date and Time Overlap<\/a><\/p>

How do I create time of day columns?<\/a><\/p>

Need to create a "shift" column from a time column<\/a><\/p>","bodyRaw":"[{\"insert\":\"I have decided to create a post that consolidates all of the different time based solutions that I have put together.\\nSome of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.\\n\\nI thought it would be helpful to have them all in one place instead of spread out.\\n\\nI welcome all feedback, suggestions, and additions.\\n\\nI'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.\\n\\nI will also try to post various tips and tricks as I think of them as well such as:\\nIt is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:\\n=VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) + IF(CONTAINS(\\\"p\\\", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) = 12, -12))\\n\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662\"},\"insert\":\"Calculating Time Worked for Employees\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc\"},\"insert\":\"Can you calculate time in Smartsheet?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81\"},\"insert\":\"Conversion of timezones (Solved Formula Included)\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795\"},\"insert\":\"Date\\\/Time\\\/Year\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd\"},\"insert\":\"Flagging a Date and Time Overlap\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767\"},\"insert\":\"How do I create time of day columns?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=54f270e0ed1440f6a25de2453119655c\"},\"insert\":\"Need to create a \\\"shift\\\" column from a time column\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-06-08T15:55:08+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-30T20:24:17+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\/68947\/formulas-for-calculating-time","embedType":"quote","name":"Formulas for Calculating Time"}"> https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time