Capture modified date and time stamp
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.
Answers
-
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:
-
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!
-
@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.
-
@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.
-
Try inserting it into a text number column and add this to the end of the formula:
=INDEX(..........., MATCH(..........)) + ""
-
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:
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Paul Newcome You have no idea how happy you just made our team. Thank you very much for the help!
-
Happy to help. 👍️
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!