How to calculating the age of a cell in hours

wtcf

Background -

I have a Form, which outputs as a row in a Grid, an extract of which is as follows -

The 'Date Raised' column contains aSystem-generated - Create (Date)value, and the 'Current Date/Time' column contains today's date (using the=TODAY()function).

Problem -

I am trying to find a formula-driven solution, which will populate the 'Task Age (hrs)' column with the difference in hrs between the value in ''Date Raised' and 'Current Date/Time' i.e. time elapsed in hrs.

The purpose of this is to track if a Task has been completed on time e.g. High Priority (4 biz hrs), Medium Priority (8 biz hrs), and Low Priority (24 biz hrs).

As I see it, I have the following obstacles to overcome -

• Current Date/Timecolumn - Is there a formula (e.g. something that can expand=TODAY())that will populate the date and time in the same format as the 'Date Raised'' column?
• Task Agecolumn - if the above is possible, how then would I compute the difference (in hrs) between the 'Date Raised' and ''Current Date/Time' column.

Thank you in advance, for any assistance anyone can offer.

Bassam Khalil

Hi @wtcf

Hope you are fine, Unfortunately, this feature is not currently available. it's a good idea and you can submitSmartsheet Product Enhancement Requestsusing this form.

Andrée Starå

Hi@wtcf

I hope you're well and safe!

Please have a look at my post below with a method I developed that probably could be modified to work for your use case.

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-08-12T23:38:51+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 day!

Best,

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

✅我的帖子(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!

wtcf

Hi@Andrée Starå- thanks for your response. Unfortunately, I haven't been able to understand your post, especially, it would be great for more context, and how I can apply the outlined principles to my use case?

Andrée Starå

Hi@wtcf

I hope you're well and safe!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

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