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: 

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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

https://community.smartsheet.com/discussion/comment/302231#Comment_302231

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

@ShaunW Hello 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

hi @BethWork so 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: 

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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 Newcome I was trying to follow along your thread here https://community.smartsheet.com/discussion/79654/how-to-input-a-date-time-and-calculate-a-time-elapsed because 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.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Grace Qin

https://community.smartsheet.com/discussion/comment/362409#Comment_362409

Hi @BethWork

Did you have an solution for this?

I tried to use helper sheet, but the creation date coming back from the helper sheet is wrong.

In the helper sheet, the creation time is 10/06/23 6:04PM, but the Vlookup in the original sheet show 10/7/23.

Do you know how to resolve it?

Thank you!