Capture modified date and time stamp

Christopher Pretty

Hello. I have an system column that records modified date and time I also have one that records when the entry was added to the Smartsheet.

We have 4 hours to respond to a new entry. What I would to do is when the status of a row changes to reviewing it would record the date and time stamp then we would use a formula to pull out the total time it has been open for before being changed to reviewing.

I created an automation to copy the row to another sheet when changed to reviewing, this way I have the correct "modified" time and it doesn't get changed again. I figure then I would use a formula to calculate the time difference and then Index/Match to look up the value and populate it on the main sheet. Whenever I try a formula to get the time difference I get an invalid operation error.

Paul Newcome

Smartsheet does not currently recognize time as numbers. It is recognized as text strings.

Here is a thread with a number of time based solutions that will help you calculate the difference:

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

Leighton W

Hi Christopher,

Would you mind elaborating on your solution of locking the modified column with a date and time after changing the status of another column (I.e "reviewing") ? That set up is exactly what I am trying to accomplish in my own sheet... To have the created date and time column and then then have a resolved column with the date and time after a box is checked.

Your guidance would be greatly apprecieated!

Thank you!

Paul Newcome

@Leighton W You would need to have some kind of unique identifier on every row. Then you can set up a copy row automation to copy the row over to another sheet when the desired trigger occurs. From there you would use an INDEX/MATCH formula to reference the sheet you are copying to and pull that data back in based on the unique identifier.

Leighton W

@Paul Newcome Thank you for that information, when i perform that task, I am getting the error of #Invalid Column Value. I want to import the modified date and time after it has been copied to the secondary sheet so that it does not change on the main sheet. Is there something else that needs to happen other than an index/match formula to make sure the column can record the modified date and time?

Thank you so much for your help.

Paul Newcome

Try inserting it into a text number column and add this to the end of the formula:

=INDEX(..........., MATCH(..........)) + ""

Andrée Starå

Hi @Leighton W

I hope you're well and safe!

Please have a look at my post below with a method I developed. (you can request to get shared to a copy)

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 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Leighton W

@Paul Newcome You have no idea how happy you just made our team. Thank you very much for the help!

Paul Newcome

Happy to help. 👍️

Jed Rusyniak

@Leighton W I'm running into a similar issue. I'm in the development phase of this for my customer and would love to see how you made this work. If you are willing would you be open to sharing me to your workflow or a copy of your workflow so I can better understand how you made this all work. I think i need to recreate the exact same thing so that i can capture if we are reacting to our customer tickets that are submitted via Smartsheet within their 2 hour SLA. Thanks so much!